Power BI: Changing multiple slicer relationship to be OR not AND

Question

In Power BI you can have multiple slicers based on different criteria in the data, e.g. one on shape and one on colour. The interaction between or among these slicers means that if I were to select "circle" and "red" then the results would be about red circles only. It filters to those entries where BOTH slicers are met. Is there a way to set Power BI slicers so that if I selected "circle" and "red" I would get both all red and all circle results (e.g. only one slicer needs to be satisfied)?

Here I'd like to get rows 1,4,5,6 returned.

Any help would be hugely appreciated!

A couple related posts: https://stackoverflow.com/questions/55544566/updating-measure-calculation-to-apply-or-logic-in-dax and https://stackoverflow.com/questions/48430572/calculate-with-or-condition-in-two-tables/48431081#48431081 — Alexis Olson, Sep 06 '19 at 14:30

greggyb · Answer 1 · 2019-09-06T14:57:48.210

Edited. As Alexis points out in the comments, I had this wrong at first. Thanks for the gentle nudge!

EitherCondition =
VAR MatchesColour =
    CALCULATE (
        COUNTROWS ( 'FactTable' ),
        KEEPFILTERS ( TREATAS ( VALUES ( 'ColoursDisconnected'[Colour] ), 'Colours'[Colour] ) )
) > 0
VAR MatchesShape =
    CALCULATE (
        COUNTROWS ( 'FactTable' ),
        KEEPFILTERS ( TREATAS ( VALUES ( 'ShapesDisconnected'[Shape] ), 'Shapes'[Shape] ) )
    ) > 0
RETURN
  MatchesColour || MatchesShape

With no other details of your data model, I'm assuming a simple dimensional model with a 'Colours' dimension of unique colours and a 'Shapes' dimension of unique shapes, each connected in a 1:N relationship with a fact table named 'FactTable'. As Alexis pointed out in comments, to drive this behavior, we need to build disconnected tables to drive the slicers.

Thus we have slicers populated from disconnected tables, and we have the table visual created with the related dimensions.

The first VAR says whether there is any data in the fact based on diconnected colour context. The second does the same for shape. If either is true, we return TRUE.

You can filter your visual on [EitherCondition]=True to remove the other values. Or you could build from here into a more complex measure.

The key part is that filter context in DAX is always a logical AND situation. The only way to get logical ORs is to evaluate multiple expressions and come up with a way to combine them that works in your situation.

Here's the model diagram:

And the measure in action:

If the slicers are set on tables that filter the fact table via relationships, won't they automatically filter the visual? I thought you needed disconnected parameter tables to do OR logic. — Alexis Olson, Sep 06 '19 at 14:35
Ack, good catch. This was a pre-coffee answer. Updating. Thanks! — greggyb, Sep 06 '19 at 14:40
That's awesome - thank you for your clear comments and example. Exactly what I was after — user3614361, Sep 09 '19 at 22:14

Power BI: Changing multiple slicer relationship to be OR not AND

1 Answers1