budding pro
budding pro

Reputation: 195

return only values from a categorical variable after filtering values from a calculation in powerbi dax

I have the following table created by a calculated measure:

+---------+-------+
|      col| %_calc|
+---------+-------+
| personID|   0.93|
|    empNO|   0.36|
|   gender|   0.65|
|  address|   0.55|
|    ccard|   0.93|
+---------+-------+

where %_calc = sum according to a condition/sum of all values. What I would like to do is to return only 'col' values that has %_calc < 0.6. I intend to use the value in a table visual showing only the column values that meets the condition. That means,

+---------+-------+
|      col| %_calc|
+---------+-------+
|    empNO|   0.36|
|  address|   0.55|
+---------+-------+

but in table visual, or the multi-row card, I only needed to show the words 'empNO' and 'address', without their corresponding values, because their values are less than 0.60 like below:

+---------+
|      col|
+---------+
|    empNO|
|  address|
+---------+

I tried the following:

cols_below60 =
RETURN 
    SWITCH(TRUE(),
        %_calc < 0.6, SELECTEDVALUE(table[fields]))

but what I get when I put the measure in a matrix looks like this:

+---------+-------+
|      col| %_calc|
+---------+-------+
|    empNO|  empNO|
|  address|address|
+---------+-------+

I also tried the following RETURN statement:RETURN %_calc < 0.6 but returns a boolean table like below :

+---------+-------+
|      col| %_calc|
+---------+-------+
| personID|  False|
|    empNO|   True|
|   gender|  False|
|  address|   True|
|    ccard|  False|
+---------+-------+

The closest I can make it partially work is the RETURN SWITCH TRUE from above. May I also add, I get the fields I needed to put the fields values and the cols_below60 to get the fields that are less than 60.

Update ##### using the second suggested solution, it is not working as expected.

enter image description here

from above, it is expected to see Expiration only because that is the only field less than 0.6, but I got all the other columns which were not supposed to be.

Upvotes: 0

Views: 475

Answers (1)

davidebacci
davidebacci

Reputation: 30324

SELECTCOLUMNS( FILTER('Table', 'Table'[%_calc] < 0.6), "col", 'Table'[col]) 

Upvotes: 1

Related Questions