Multiple Slicers-Use “OR” Condition To Visualize Data

I did a presentation on paginated reports at the Vancouver Modern Excel and Power BI meetup on Jan 21, 2021 (BTW – thanks if you joined! Feel free to check out the recording here if you missed it). There, a question was posed regarding how “OR” can be used when filtering on two different parameter columns, or when using Power BI slicers (such as filtering on Customers and Products), to show BOTH the selected customers and selected products. This is in contrast to what usually occurs, which is that if you selected Customer 1 and Product B, your output would show only the Product B sales for Customer 1. We want to figure out a way to show ALL of the sales to Customer 1 and ALL the sales of Product B when filtering on these two parameters.

Problem Summary

For example, assume Customers A and B are selected and Products P1 and P2 are selected in their respective slicers. We want to show all the products sold to Customers A and B, and also show the sales of all P1 and P2 products sold (regardless of the customer who purchased the product).

In this blog post, I’ll be sharing some techniques to help you achieve this functionality. Disconnected tables will be very helpful in working toward this goal. More often, I have been finding that many complex and/or unusual tasks are solved through the use of disconnected tables and DAX (I mean… who doesn’t love DAX?). We can see that this is certainly the case for this solution, so it’s a very good technique/skill to practice!

What is a disconnected table?

There is nothing that special about disconnected tables, nor is there any property in the model which is explicitly set to say that the table is a disconnected table. In simple terms, all it means is that the tables don’t have relationships (they are not linked together). Values from disconnected tables are used in DAX expressions to conform to the business logic. When a table does not have a relationship with any other table and you use this disconnected table in a slicer or filter, no other table will be filtered because it doesn’t have a relationship with anything. In this particular use case, we have two disconnected tables: Customer Slicer and Product Slicer. We will use these tables in our slicers. By selecting values in these slicers, our Sales table won’t be filtered. When users make the Customer or Product selection in the slicers, we will get the selected values from these slicers and then our “OR” condition logic will be used to filter the Sales table.

In the image below, the two tables in the RED boxes are the disconnected tables. As you can see, they don’t have a relationship with any of the other tables in the model.

In our example, we have a typical star schema. The Sales fact table connects to Customers, Product, Calendar, and some other dimensions.

Solution – Using Power BI Slicers from the disconnected tables and a DAX Measure:

We first need to create two disconnected tables, one for Customer and one for Products. I did this in Power Query by referencing the Customer and Products tables. I named these new tables as follows: Customer Slicer and Product Slicer.

The most important thing to note is that we will be using the Customer and Product columns in the slicer from the disconnected (Customer Slicer and Product Slicer) tables rather than from our regular dimension tables (Customer and Products).

I then added the following measure to get this solution working. Although this DAX expression may look very long and complicated, it is actually very simple. Below, I have explained everything line by line so you can see what the code is doing.

Sales = 
//get list of all customers of selected products
VAR __isProductFiltered = ISFILTERED ( 'Products Slicer'[Product] ) //is product has filter
VAR __isCustomerFiltered = ISFILTERED ( 'Customers Slicer'[Customer] ) //is customer has filter
VAR __isNothingSelected = NOT __isProductFiltered && NOT __isCustomerFiltered //is both customer and product are not selected
VAR __getProducts = ( NOT ( __isCustomerFiltered && NOT __isProductFiltered ) ) || __isNothingSelected 
VAR __getCustomers = ( NOT ( __isProductFiltered && NOT __isCustomerFiltered ) ) || __isNothingSelected
VAR __customersforSelectedProduct = 
//IF ( __getProducts,
    SUMMARIZE ( 
        FILTER ( 
            Sales,
            Sales[ProductKey] IN VALUES ( 'Products Slicer'[ProductKey] ) 
        ),
        Sales[ProductKey],
        Sales[CustomerKey]
    )
//get list of all products of selected customers
VAR __productsforSelectedCustomer = 
    SUMMARIZE ( 
        FILTER ( 
            Sales,
            Sales[CustomerKey] IN VALUES ( 'Customers Slicer'[CustomerKey] ) 
        ),
        Sales[ProductKey],
        Sales[CustomerKey]
    )

//combine both table and get the distinct customers and product 
VAR __filterSales = 
DISTINCT (
    UNION ( 
        FILTER ( __customersforSelectedProduct, __getProducts ),
        FILTER ( __productsforSelectedCustomer, __getCustomers )
    )
)
RETURN
//filter sales for cutomers and products
CALCULATE ( 
    SUM ( Sales[Total Sales Amount] ), 
    TREATAS ( __filterSales, Sales[ProductKey], Sales[CustomerKey] ) 
)

How the above measure works:

• __isProductFiltered = True if any value is selected in the Product Slicer, otherwise false.
• __isCustomerFiltered = True if any value is selected in the Customer Slicer, otherwise false.
• __isNothingSelected = True if no value is select in the Product and Customer Slicers, otherwise false.
• __getProducts = This variable will either be true or false, which will determine if we need to apply a product filter or not. If a value is selected in the Customer Slicer but no product value is selected in the Product Slicer, then it will be false (we don’t want to show sales of all the products). In this case, we only want to show the sales to the selected customers. However, if no products and no customers are selected then we will show the sales of all the products.
• __getCustomers = Similar to __getProducts, we are applying a similar logic for customers.
• __customersforSelectedProduct = Gets the list of customers and products from the Sales table for selected products in the slicer.
• __productsforSelectedCustomer = Gets the list of customers and products from the Sales table for selected customers in the slicer.
• __filterSales = In this variable, we will store our final table that will filter the Sales table. We combine the __customersforSelectedProduct and __productforSelectedCustomer tables and get the distinct rows, and also check if __getProducts is false, then don’t get any record from __customersfromSelectedProducts, and if __getCustomers is false, then don’t get any records from __productsforSelectedCustomer.

In the RETURN statement, we filter the Sales table using our __filterSales table to achieve the result.

As you can see, a lot can be achieved just by using disconnected tables and some DAX measures. What are your thoughts on this solution? Comments are always welcome!