Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I am new to Power BI, and am struggling to figure out how to allow a user to change the condition between two selection slicers.
My report is sourced from a single table that contains patients transfers.
There is a 'Sending' field (where the patient was transferred from) and 'Receiving' field (where the patient was transferred to) in the table. I've created a selection slicer for each field. I'm going to over simplify with a dummy table below, but it will be sufficient to describe what I'm trying to do.
Transfer ID Sending Receiving
1 ACME Hospital General Hospital
2 ACME Hospital Metro Hospital
3 City Hospital ACME Hospital
4 Big Hospital General Hospital
5 Super Hospital ACME Hospital
How do allow users to select what they are wanting for the following scenarios?
Show data for all transfers where the Sending was 'ACME Hospital' or the Receiving was 'ACME Hospital'? (This should show Transfer IDs 1, 2, 3, & 5)
Likewise, what if the user then would like to use these 2 slicers to show all transfers where the Sending was 'ACME Hospital' and the Receiving was 'General Hospital' ?
If the relation between the slicers is an 'OR' condition, then it would list Transfer ID 1, 2 & 4. Alternatively, if the relation between the slicers is an 'AND' condition then it would only list Transfer ID 1.
I know that I can edit the interactions between the two slicers, so they either effect each other or they don't...but that is a static setting as I understand it, and not something the user will be able to do in the Power BI service once the report is published there.
Trying to figure out how to allow the user to manipulate the behavior between the slicers, depending on what they are wanting to see.
I'm thinking it is probably something simple, but not sure how to approach it!
Any ideas would be much appreciated.
Solved! Go to Solution.
Hi @bldorris
One way of acheiving this is (sample pbix here😞
Slicer Behavior = SELECTEDVALUE ( 'Slicer Option'[Slicer Option], "And" ) Inclusion Flag = SWITCH ( [Slicer Behavior], "And", CALCULATE ( COUNTROWS ( Transfers ), USERELATIONSHIP ( Transfers[Receiving], Receiving[Receiving] ), USERELATIONSHIP ( Transfers[Sending], Sending[Sending] ) ), "Or", CALCULATE ( COUNTROWS ( Transfers ), USERELATIONSHIP ( Transfers[Receiving], Receiving[Receiving] ) ) + CALCULATE ( COUNTROWS ( Transfers ), USERELATIONSHIP ( Transfers[Sending], Sending[Sending] ) ) )
Result looks like this:
Regards,
Owen
Hi @bldorris
One way of acheiving this is (sample pbix here😞
Slicer Behavior = SELECTEDVALUE ( 'Slicer Option'[Slicer Option], "And" ) Inclusion Flag = SWITCH ( [Slicer Behavior], "And", CALCULATE ( COUNTROWS ( Transfers ), USERELATIONSHIP ( Transfers[Receiving], Receiving[Receiving] ), USERELATIONSHIP ( Transfers[Sending], Sending[Sending] ) ), "Or", CALCULATE ( COUNTROWS ( Transfers ), USERELATIONSHIP ( Transfers[Receiving], Receiving[Receiving] ) ) + CALCULATE ( COUNTROWS ( Transfers ), USERELATIONSHIP ( Transfers[Sending], Sending[Sending] ) ) )
Result looks like this:
Regards,
Owen
This is marvelous!
Thanks Owen! Going to give this a go this weekend and let you know how it goes. I was so focused on finding a setting within Power BI, and didn't think about having separate lookup tables.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
87 | |
82 | |
64 | |
49 |
User | Count |
---|---|
124 | |
110 | |
88 | |
68 | |
66 |