Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |