Allow user to change condition between two slicers

[bldorris]

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.

[OwenAuger · Accepted solution]

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, 
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:

Regards,

Owen

[Penn]

This is marvelous!

[bldorris]

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.