topic Measure to check if ID has any Type selected from Slicer in DAX Commands and Tips

Measure to check if ID has any Type selected from Slicer

RyanHare92 — Thu, 21 Sep 2023 10:40:46 GMT

I have a table that looks like this:

ID	Type
1	A
1	B
1	C
2	B
2	B
2	C
3	C
3	A
4	D

I would like to create a measure that tells me, based on a slicer selection that has option from another table with the unique "Type" values, if each ID contains more than one of the selected Type.

The outcome I would like to have, assuming that "A" is selected in the slicer, it the following:

ID	Type	Measure_Outcome
1	A	Yes
1	B	Yes
1	C	Yes
2	B	No
2	B	No
2	C	No
3	C	Yes
3	A	Yes
4	D	No

I have put the following together, but it doesnt seem to work. I believe I have a basic misunderstanding of how Measures work:

Measure = VAR _CHECK = CALCULATE( COUNTROWS(Table), Table[Type] = MAX(Sources[Type]), Table[ID] = MAX(Table[ID]) ) RETURN IF( _CHECK > 0, "Yes", "No" )

The Source table is the table that includes a summarized table of all the Types.

Would anyone be able to help me create a functioning measure?

Many thanks in advance.

Re: Measure to check if ID has any Type selected from Slicer

tamerj1 — Thu, 21 Sep 2023 10:50:09 GMT

Hi RyanHare92

Please try

ResultMeasure = IF ( ISEMPTY ( INTERSECT ( CALCULATETABLE ( VALUES ( 'Table'[Type] ), ALLSELECTED ( 'Table' ), VALUES ( 'Table'[ID] ) ), VALUES ( Sources[Type] ) ) ), "No", "Yes" )

Re: Measure to check if ID has any Type selected from Slicer

RyanHare92 — Thu, 21 Sep 2023 10:57:57 GMT

Hi Tamerj1,

Word like a charm! Thank you so much. Now I just need to understand how it works 😅

Re: Measure to check if ID has any Type selected from Slicer

tamerj1 — Thu, 21 Sep 2023 11:51:59 GMT

RyanHare92
Maybe the following calrifies it a little bit. This is how it behaves in the first three rows (i.e. for ID = 1):

IF ( ISEMPTY ( INTERSECT ( { "A", "B", "C" }, { "A" } ) ), "No", "Yes" ) >>>>> IF ( ISEMPTY ( { "A" } ), "No", "Yes" ) >>>>> IF ( FALSE, "No", "Yes" ) >>>>> "Yes"