Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I'm trying to make a measure that returns 1 for all visits that have a related prestation, let's say A, but don't have another certain prestation, let's say B. The data, in a simplified version, is as follows:
VISIT_ID PRESTATION
1 A
1 B
1 C
2 A
2 C
3 B
3 C
So in this case, I would want to return 1 for visit 2, but not for visits 1 and 3.
The measure for this is straightforward, I would use:
Solved! Go to Solution.
@E__ You will need 2 disconnected slicer tables to run your slicers. Then you can use SELECTEDVALUE to grab the value selected.
You can create these slicer tables using:
Slicer Table 1 = DISTINCT(SELECTCOLUMNS('Table',"PRESTATION",[PRESTATION]))
Slicer Table 2 = DISTINCT(SELECTCOLUMNS('Table',"PRESTATION",[PRESTATION]))
After you create them, go to the model view and make sure that any relationships to these tables are removed
Following on from @Greg_Deckler suggestion, if you want to allow for a multi-selection in either slicer, you can do the following.
First the model:
(Just FYI, this will also work if you have Slicer 1 related to your fact table in a one-to-many relationship)
Next, create a measure as follows:
Countrows non-coinciding "Prestations" =
VAR Pres1 = CALCULATETABLE(VALUES('Fact'[VISIT_ID]),
TREATAS(VALUES('Slicer 1'[Prestation1]), 'Fact'[ PRESTATION]))
VAR Pres2 = CALCULATETABLE(VALUES('Fact'[VISIT_ID]),
TREATAS(VALUES('Slicer 2'[Prestation2]), 'Fact'[ PRESTATION]))
RETURN
COUNTROWS(EXCEPT(Pres1, Pres2))
And you will get this:
I've attached th PBIX file for you reference.
Proud to be a Super User!
Paul on Linkedin.
Thank you both for your suggestions! This will definitely solve my problem.
Best,
Eva
Following on from @Greg_Deckler suggestion, if you want to allow for a multi-selection in either slicer, you can do the following.
First the model:
(Just FYI, this will also work if you have Slicer 1 related to your fact table in a one-to-many relationship)
Next, create a measure as follows:
Countrows non-coinciding "Prestations" =
VAR Pres1 = CALCULATETABLE(VALUES('Fact'[VISIT_ID]),
TREATAS(VALUES('Slicer 1'[Prestation1]), 'Fact'[ PRESTATION]))
VAR Pres2 = CALCULATETABLE(VALUES('Fact'[VISIT_ID]),
TREATAS(VALUES('Slicer 2'[Prestation2]), 'Fact'[ PRESTATION]))
RETURN
COUNTROWS(EXCEPT(Pres1, Pres2))
And you will get this:
I've attached th PBIX file for you reference.
Proud to be a Super User!
Paul on Linkedin.
@E__ You will need 2 disconnected slicer tables to run your slicers. Then you can use SELECTEDVALUE to grab the value selected.
You can create these slicer tables using:
Slicer Table 1 = DISTINCT(SELECTCOLUMNS('Table',"PRESTATION",[PRESTATION]))
Slicer Table 2 = DISTINCT(SELECTCOLUMNS('Table',"PRESTATION",[PRESTATION]))
After you create them, go to the model view and make sure that any relationships to these tables are removed
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |