Power BI: Implement AND/OR Selection

Power BI is really growing on me. Using this software everyday, I am amazed by the capabilities of this product and the way it improves month after month by adding new features or optimizing current capabilities.

Power BI desktop. (Source: microsoft.com)

However, like any software solution, there is still room for improvement and a lack of some features that would benefit many users. One of these possible improvements would be enabling the type of multi-selection. In the current setup, Power BI treats multi select like “OR” instead of “AND”. Let me quickly walk you through the difference.

AND vs. OR

The concept of AND/OR is very simple, but for those not familiar I will use a simple example to demonstrate. Let say you have a simple table with students and their language courses like this:

AND/OR data example

French OR English: Student A, B, C
French AND English: Student A, B

The default Power BI behavior is treating this selection as OR.

In this article, I will walk you through the setup of having both an AND and OR selection in your own Power BI dashboard.

The sample data I created for this demo looks like this:

Data for demo. 2 tables in total

The first table contains basic information about some students, while the other table has multiple rows per student, containing information about the language courses they are taking. The tables are connected on a student ID, in a bidirectional one-to-many relationship.

Let’s write some code

Before we start writing the code you should be familiar with the DAX basics. For those who are not, DAX is a programming language you can use within Power BI to create filtering, calculations, columns and much more.

First, we will create a very small table containing the indicators and values we will later use to create an ‘AND/OR’ slicer. It is a 2x2 table which you can add from an external source, or simply create it with this code:

ANDOR_Table = 
  DATATABLE(
    “AND/OR”, STRING,
    “Binary”, INTEGER, 
     { 
       {“AND”, 0},
       {“OR”, 1}
     }
   )```

The table should look something like this:

AND/OR table, used for the slicer

Before we start writing a measure, I recently adopted the good practice of placing all your measures in the same table in order to keep things clean. So let’s first create an empty table like this:

_Measures = 
    {BLANK()}

In this table add the following measure:

Selected_AndOr = 
  MAX(ANDOR_Table[Binary]) 

This measure will indicate whether you select AND or OR selection. You can already add a slicer to the report containing the AND/OR strings as items.

AND/OR slicer

Now we have everything for the selection, it’s time to start creating the backend. The next measure we will create is the one that will count how many rows you selected.

Selected_counter = 
  IF( 
     ISFILTERED(‘Courses’[Course]), 
     COUNTROWS( 
           ALLSELECTED(‘Courses’[Course]) 
               ),
      0)

The counter we created in the previous measure will now be used to add an indicator to the table where you want to see the results of your selection, in this case the Student table.

The last measure we create is the one that will use the previous measure in order to see if the row satisfies the ‘AND’ criteria. It will return a 0/1 flag.

Course_ANDOR_Check = 
   IF(
      [Selected_counter] = 0, 
       1, 
       IF(
          DISTINCTCOUNT(‘Courses’[Course]) = [Selected_counter], 
          1,
          _Measures[Selected_AndOr]
           )
       )

Now everything is set up, it’s time to add it to the visual. For each widget you want to get affected by this functionality you should add a filter on the visual level like this: Course_ANDOR_Check IS 1

Filter you should apply to the visuals

DONE! There you have it, you can now use the AND/OR slicer to change the functionality as you can see in the video below.

Final result

This solution is a combination of my own code and snippets I found online. As I am still developing my DAX skills, I am eager to improve this code. So please feel free to leave your comments if you have any questions or ideas to improve this solution.

About me: My name is Bruno and I work as a data scientist with Dashmote, an AI technology scale-up headquartered in The Netherlands. Our goal is bridging the gap between images and data thanks to AI-based solutions.
Check out my other work on https://www.zhongtron.me.