Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Hi @RyanHare92
Please try
ResultMeasure =
IF (
ISEMPTY (
INTERSECT (
CALCULATETABLE (
VALUES ( 'Table'[Type] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[ID] )
),
VALUES ( Sources[Type] )
)
),
"No",
"Yes"
)
Hi @RyanHare92
Please try
ResultMeasure =
IF (
ISEMPTY (
INTERSECT (
CALCULATETABLE (
VALUES ( 'Table'[Type] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[ID] )
),
VALUES ( Sources[Type] )
)
),
"No",
"Yes"
)
Hi Tamerj1,
Word like a charm! Thank you so much. Now I just need to understand how it works 😅
@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"
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
43 | |
26 | |
21 |