Skip to main content
cancel
Showing results for 
Search instead 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

Reply
tt211595
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:

IDColor

1

red

2

green
3red
4red
5blue
6yellow

 

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:

IDColorMyMeasure

1

redTRUE

2

greenFALSE
3redTRUE
4redTRUE
5blueFALSE
6yellowTRUE

 

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
rajendraongole1
Community Champion
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!
Appreciate your Kudos!!

View solution in original post

2 REPLIES 2
connect
Helper II
Helper II

Please use the following measure: 

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

rajendraongole1
Community Champion
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!
Appreciate your Kudos!!

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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