Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
bldorris
New Member

Allow user to change condition between two slicers

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.

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @bldorris

 

One way of acheiving this is (sample pbix here😞

  1. Set up data model like this, with Sending/Receiving lookup tables connected to Transfers with inactive relationships, image.png

     

  2. Create a 'Slicer Option' table with values And/Or to choose the slicer behaviour.
  3. Create these measures. The Inclusion Flag looks at whether And/Or is selected. If And is selected, both Sending/Receiving filters are applied simultaneously and the # rows of Transfers returned. Otherwise, they are applied separately and the row counts summed.
    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] )
        )
    )
  4. For the visual you want to filter (e.g. a table showing rows of Transfers) add a visual level filter Inclusion Flag > 0 or Inclusion Flag is not blank.
  5. Add a slicer for 'Slicer Option'[Slicer Option] to choose between And/Or.

Result looks like this:image.png

 

 

Regards,

Owen

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

Hi @bldorris

 

One way of acheiving this is (sample pbix here😞

  1. Set up data model like this, with Sending/Receiving lookup tables connected to Transfers with inactive relationships, image.png

     

  2. Create a 'Slicer Option' table with values And/Or to choose the slicer behaviour.
  3. Create these measures. The Inclusion Flag looks at whether And/Or is selected. If And is selected, both Sending/Receiving filters are applied simultaneously and the # rows of Transfers returned. Otherwise, they are applied separately and the row counts summed.
    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] )
        )
    )
  4. For the visual you want to filter (e.g. a table showing rows of Transfers) add a visual level filter Inclusion Flag > 0 or Inclusion Flag is not blank.
  5. Add a slicer for 'Slicer Option'[Slicer Option] to choose between And/Or.

Result looks like this:image.png

 

 

Regards,

Owen

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.