Measure with rowwise IN operator

[tt211595]

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!

[connect]

Please use the following measure: 

MyMeasure =
VAR SelectedColors = VALUES('AllColors'[Color])
RETURN
IF (
COUNTROWS (
FILTER (
SelectedColors,
SelectedColors =MyTable[Color]
)
) > 0,
TRUE,
FALSE
)

[rajendraongole1 · Accepted solution]

Hi @tt211595  - Create a table that contains all unique colors

uniquecolors = VALUES('MyTable'[Color])

create a measure and pass your uniquecolors :

MyMeasure =
VAR SelectedColors = VALUES('AllColors'[Color])
RETURN
IF(
SELECTEDVALUE('MyTable'[Color]) IN SelectedColors,
TRUE,
FALSE
)

Ensures that the measure MyMeasure evaluates each row of MyTable and returns TRUE if the color is selected in the slicer otherwise FALSE

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!