Implementing slicers with an OR relationship in Power BI

Actually – I am not sure if my title is correct, because I am having a difficult time naming the scenario 😊. But here’s a short description of what we had to achieve in one of the models, we recently worked on. It is a traditional dimensional model with a fact table containing package movements from one office to another (one fact or row in the table contains a movement of a package from Office A to Office B).

Original data model

Here is a snapshot of the data:

Sample data

The task in hand was to implement a model, that allows filtering for offices that are either the sending or receiving part of a package. Something like this:

  • If a user filters for Office 101, then the result should be all packages where Office 101 is on the sending or receiving part
Use case 1
  • If a user filters for Office 102
Use case 2
  • If a user filter for Office 103
Use case 3

In the original model, where I had two Office dimensions (one for the sending and one for the receiving part), there is generally one straight-forward approaches one can implement to achieve the task.

  • Use only one of the dimension tables (for instance Office From) and implement an inactive relationship to the Office To Key column in the fact.  

Unfortunately, this would require business users and modelers to create extra Measures and generally increase the complexity of creating reports. And because in our case we had to implement a more intuitive approach, that uses less DAX, we decided to implement a slightly different strategy.

The core of our idea was related to constructing an additional table, that contains each available pair for Sender/Receiver office and mapping it to a column, representing the office that needs to be filtered. Something like this:

  • Create a mapping table that contain all available pairs
  • Add a column that represents the Office to be filtered (this will require to duplicate the mapping table rows)
  • Add a concatenated column in the Fact and Mapping table that has Sender/Receiver offices concatenated (Store Filter column below)
Desired result

If we examine Use Case 1: User filters for Office 101 (marked with red on the schematics), the Mapping table will return 3 available pairs for filtering and they will propagate to the fact table. Result will be packages with numbers 1, 2 and 3

If we examine Use Case 2: User filters for Office 102 (marked with blue on the schematics), the Mapping table will return 2 available pairs for filtering and they will propagate to the fact table. Result will be packages with numbers 1 and 2.

The implementation steps:

  1. Create the mapping table. This can be done in T-SQL (during import, if you are using a SQL Server/SQL DB, etc.), PowerQuery or in DAX. The choice depends on several different factors, that are outside the scope of this article. In our case we chose to create a view on the database end to construct that mapping table, thus utilizing existing indexes and significantly speeding up the process
  2. Create the additional Store Filter columns. This can also be done with T-SQL, PowerQuery or DAX
Sample from the filter column
  • Create the required relationships in the model, placing the mapping table between the Fact and the Office dimension tables. Keep in mind that this relationship will be Many-to-Many and you need to be careful with the rest of your model. If you are in the Analysis Services world (prior SSAS 2019, where M:N relationships are supported), you will have to implement an additional bridge table.
Final model

Some final thoughts – this is quite a demanding mapping table and it can become complex really quickly – if you have 1000 offices, this will require 1000*1000*2 = 2 000 000 rows for the mapping table. And if you need a combination of 3 columns (Order Date, Ship Date and Due Date for instance), then this approach might not be ideal or even possible. In any case, it’s an idea that worked in our case, evaluate it the next time you have such a modelling requirement. It might just do the job for you too.

Post Views: 3,106