Reputation: 195
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.
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
Reputation: 30324
SELECTCOLUMNS( FILTER('Table', 'Table'[%_calc] < 0.6), "col", 'Table'[col])
Upvotes: 1