cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measure with rowwise IN operator

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!

1 ACCEPTED SOLUTION
Community Champion

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!

2 REPLIES 2
Helper II

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

Community Champion

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors