Changing “OR” to “AND” Logic for Power BI Slicers

Goal: Create the necessary calculated fields to change “OR” to “AND” logic for Power BI slicers (native slicer and the hierarchySlicer).

I have been using Power BI for half a year now. Like previous BI tools I have used, a couple of hacks help to transform seemingly impossible business requirements into super happy reactions:

“Seems pretty magical that you got it to work, good job!”

“OMG, I’m actually a little giddy.”

A couple of months ago, I was asked to change the logic within a custom visual, the hierarchySlicer, from showing data based on “OR” logic to showing data based on “AND” logic. After some research and several attempts, I am happy that that the necessary hack has been found!

Completed dashboard which utilizes slicers with AND instead of OR logic for a hierarchySlicer.

Download the dashboard here.

So how do Power BI’s slicers work?

Borrowing the words from Rob Collie: The more you select, the more you “get”. As you select more items in your slicer, Power BI shows the union of your data or the distinct list of items satisfied by selecting the slicer items. With each additional slicer item selected, the total pieces of data sliced increases.

Selecting Flag Colors == ‘gold’ (Country Count = 91) and Flag Colors == ‘orange’ (Country Count = 26) causes the Country Count to increase (Country Count = 102).

Why would you want to change the logic?

Whatever the business requirement might be, at the end of the day you want to determine which members in a group have common features. You want to perform an intersection of your data. So let’s say that I am interested in knowing which countries’ flags are similar to one another based on color and details. We will expose this with two different Power BI slicers:

1. Native slicer
2. hierarchySlicer

But first let’s discuss the data

I want to be transparent on how the data is organized for replicating this approach. I restructured UCI’s Flags Data Set into two different tables, one holding the country information (countryInfo) and the other holding the flag information (flagInfo). countryInfo’s primary key (unique, does not repeat) is the name of the country and important metrics like area, population, language, etc. live in this table. The variable and detail flag data is a hierarchical structure, which lives in flagInfo. countryInfo and flagInfo have a one to many relationship on the name of the country. When repeating this for your data set, set up a similar structure to ensure correct results.

Tables countryInfo and flagInfo. Note: This data set is from 1986 and URLs for the flags were matched as best as possible, not all the countries in the data set exist today.

Case 1: Changing to “AND” Logic for Power BI’s Native Slicer

Question: Which countries’ flags have gold AND orange colors?

We will create three calculated measures which will do the following:

1. Counts the number of items selected in the slicer

Description: If an item on the slicer is selected, count the number of items selected on the slicer.

Number of colors selected on slicer = IF(ISFILTERED(flagInfo[details]), COUNTROWS(ALLSELECTED(flagInfo[details])), 0)

2. Checks if the colors selected in the slicer are in a country’s flag

Description: If no items were selected in the slicer, show all the countries. If items in the slicer were selected and the number of items selected on the slicer are equal to the number of selected colors for a country, show the countries.

PBISlicer Check = 
IF([Number of colors selected on slicer] = 0, 1,
 IF(DISTINCTCOUNT(flagInfo[details]) = [Number of colors selected on slicer], 1, 0))

3. Counts the number of countries which satisfies the ‘AND’ requirement

Description: Filter the country table only where PBISlicer Check is true. For the filtered table, count the rows of the table.

PBISlicer Country Count = 
CALCULATE(COUNTROWS(countryInfo), 
FILTER(countryInfo, [PBISlicer Check] = 1))

When creating the actual dashboard, you will want to make sure that the slicer’s “AND” logic effects every one of the dashboard visuals.

A. For card visualizations drag the recalculated Country Count. B. For table visualizations, create your table as you usually would but set PBISlicer Check is 1 as a Visual Level Filter to ensure that the list of countries will be filtered based on slicer selection.

Case 2: Changing to “AND” Logic for Power BI’s hierarchySlicer

Questions: Which countries’ flags have icon OR quarters details AND 0 stripes? For those countries, what is the total population? What is the distribution of religions for those countries?

The main thing to notice for this example is that we have two tiered logic within the hierarchy. At the top level, AND logic applies (ie. Want countries’ flags where there are details AND stripes), while at the bottom level, OR logic applies (ie. Want countries’ flags where details are icon OR details are quarters). We will create four calculated measures which will do the following:

1. Counts the number of items selected in the slicer

Description: If an item on the slicer is selected, count the number of items selected on the slicer.

Number of variables selected on slicer = IF(ISFILTERED(flagInfo[variable]), COUNTROWS(ALLSELECTED(flagInfo[variable])), 0)

2. Checks if the variables selected in the slicer are in a country’s flag

Description: If no items were selected in the slicer, show all the countries. If items in the slicer were selected and the number of items selected on the slicer are equal to the number of selected variables for a country, show the countries.

HierarchySlicer Check = 
IF([Number of variables selected on slicer] = 0, 1,
 IF(DISTINCTCOUNT(flagInfo[variable]) = [Number of variables selected on slicer], 1, 0))

3. Counts the number of countries which satisfies the ‘AND’ requirement

Description: Filter the country table only where HierarchySlicer Check is true. For the filtered table, count the rows of the table.

HierarchySlicer Country Count = 
CALCULATE(COUNTROWS(countryInfo), 
FILTER(countryInfo, [HierarchySlicer Check] = 1))

4. Calculates the population of countries which satisfy the ‘AND’ requirement

Description: Filter the country table only where HierarchySlicer Check is true. For the filtered table, calculate the sum of the population.

HierarchySlicer population = 
CALCULATE(SUM(countryInfo[population]), 
FILTER(countryInfo, [HierarchySlicer Check] = 1))

When creating the actual dashboard, you will want to make sure that the slicer’s “AND” logic effects every one of the dashboard visuals. Refer to the above case to create the previous card and table visualizations.

A. For this card visualizations drag the recalculated population. B. For all visualization except for table visualizations, you will need to drag the recalculated metric as the value for the visualization. Using Check is 1 as a Visual level filter will not work.

Recommendations

• I would not recommend having multiple ‘AND’ logic slicers on the same dashboard, as you can imagine the logic for the ‘Check’ DAX calculations can become very complicated very fast.
• Also, I would not recommend having multiple ‘AND’ logic slicers on the same dashboard since this can cause very poor performance as I had found when using 2 ‘AND’ logic hierarchySlicers in the same dashboard.
• Perform a lot of quality assurance on your data results!

Concluding Remarks

There are many other ways that you can implement the ‘AND’ logic DAX equations (ie. using variables, breaking the calculations up more, etc.). This is the approach that worked for me and scaled well in production. The data, data restructuring code, and PBIX file in the post is available here. If you have any questions or thoughts on the tutorial, feel free to reach out in the comments below or through Twitter. Also, if you would like to learn more about Seismic Software and how we use Microsoft’s Power BI, visit us.