Measure with rowwise IN operator

Question

Hello! I am trying to create a measure that returns TRUE and FALSE for each row of a table based on a slicer selection.

For example, say I have a table called MyTable with two columns named ID and Color:

ID	Color
1	red
2	green
3	red
4	red
5	blue
6	yellow

I've created a table called AllColors = VALUES('MyTable'[Color]) and I've created a slicer to allow the user to select colors. In this example, the user has selected "red" and "yellow" in the slicer.

I would like to create a measure called MyMeasure that returns TRUE if the color is one of the colors that the user selected in the slicer and FALSE if the user did not select the color. Like below:

ID	Color	MyMeasure
1	red	TRUE
2	green	FALSE
3	red	TRUE
4	red	TRUE
5	blue	FALSE
6	yellow	TRUE

I have tried using the IN operator. For example, MyMeasure = MyTable[Color] IN VALUES(AllColors[Color]), but that does not seem to work at the rowwise level (like functions such as SUMX, AVERAGEX, etc might work).

Is it possible to create a measure like I want? Thank you!

rajendraongole1 · Accepted Answer

Hi&nbsp;tt211595&nbsp; -&nbsp;Create a table that contains all unique colorsuniquecolors = VALUES('MyTable'[Color])create a measure and pass your uniquecolors :MyMeasure =VAR SelectedColors = VALUES('AllColors'[Color])RETURNIF(SELECTEDVALUE('MyTable'[Color]) IN SelectedColors,TRUE,FALSE)&nbsp;Ensures that the measure MyMeasure evaluates each row of MyTable and returns TRUE if the color is selected in the slicer otherwise FALSE&nbsp;Did I answer your question? Mark my post as a solution! This will help others on the forum!Appreciate your Kudos!!

Forum Discussion

Measure with rowwise IN operator

2 Replies

Related content

SQL Operator with multiple destinations

Use Wildcard Operator

Question mark as operator

Arithmetic Operation Overflow

Rowwise minimum without exact column name