How to create an advanced filter experience for users

[Anonymous]

• Overview
• About this approach
• Limitations - performance with large data sets
• How it works
• Data model setup
• Step 1: Data prep
• Step 2: Create core data model
• Step 3: Create tables for AND/OR slicer options
• Step 4: Create the following core measures
• Step 5: Create report measures using the “ANDORNOT Logic” measure as a FILTER
• Step 6: Create front end report

Overview

About this approach

There are a number of examples that have shown how to add logic so that you can use filters with an "AND" operator rather than the standard "OR" logic when using a slicer to filter a main table. This example shows how to also add the ability for a "NOT" statement as well, to create what I think is an incredibly powerful level of flexibility for end users.

In this example, we can see that we have been able to filter to items with all of the attributes selected in the INCLUDE slicer and neither of the attributes selected in the EXCLUDE slicer (although they do still come through if they have only one of the attributes, which would change if the operator selected was OR)

The attached example has been built using open data and shows how the approach works

Download PBIX example 

Limitations - performance with large  data sets

This solution does work but there are likely opportunities to improve performance of these measures. In examples where the attribute tables have millions of rows performance can be slow, the current approach can result in a very large number of engine queries and depending on capacity in Power BI server can even result in visuals timing out due to lack of memory.

How it works

Follow the below process to create a similar filter experience in your reports. The example shown has been created using example data.

The approach I have used is based on the following excellent blog post.

Power BI: Implement AND/OR Selection | by ZhongTr0n | Towards Data Science

In my example, I have extended the logic to also allow users to EXCLUDE items. The core logic is similar, and the EXCLUSION criteria can be applied using either an OR logic or AND logic operator (i.e. Filter for Items that DON'T have X OR Y attribute versus Items that DON'T have X AND Y attribute)

Data model setup

Step 1: Data prep

To create this approach, you require 2 types of table:

1. Master table – this contains the unique list of items that you are looking to filter – this could be customers, products etc… in the example, I have used geographic areas in the UK called LSOAs (Lower Super Output Areas)
2. Attribute table – this table has 3 core elements to it
   1. Link ID – to map back to the master table
   2. Attribute description – to describe the type of characteristic we are looking at, this can be anything and can be used to distinguish between multiple different types of
   3. Attribute – for each unique item, whether or not they have the particular characteristic

Benefits of this approach: the attribute table is scalable, and can be set up to contain a range of different categories by unioning tables together into the same structure, which you can see in the example if you go to power query. The advantage of this is that if you want to add new attributes to the register, you can do so by editing the data source, and these new options will then appear in the front end without having to make any changes to either the data model or front end report.

 

Step 2: Create core data model

The data model requires 3 copies of the attribute table you wish to use for the filtering, which then all link to a master table of your unique items as shown below. This master table can then be joined on to various other fact tables or contain facts themselves that will be used in further calculations.

Step 3: Create tables for AND/OR slicer options

Add the following tables:

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

ANDOR_Table (NOT) =
  DATATABLE(
    "AND/OR", STRING,
    "Binary", INTEGER,
     {
       {"AND", 0},
       {"OR", 1}
     }
   )

Step 4: Create the following core measures

These key measures create the filter logic:

Determines the criteria for inclusion based on both the selections in the INCLUDE and EXCLUDE tables

AndOrLogicSwitch = IF (
    AND (
        SUM ( ANDOR_Table[Binary] ) = 0,
        ISFILTERED ( 'LSOA Attributes - Filter'[Attribute] )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'LSOA Attributes - Filter'[Attribute] ),
        ALL ('LSOA Master' )
    ),
    1
)

AndOrLogicSwitch (NOT) = IF (
    ISFILTERED ( 'LSOA Attributes - Filter (NOT)'[Attribute] ),
    IF (
        SUM ( 'ANDOR_Table (NOT)'[Binary] ) = 0,
        CALCULATE (
            DISTINCTCOUNT ( 'LSOA Attributes - Filter (NOT)'[Attribute] ),
            ALL ( 'LSOA Master')
        ),
        1
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'LSOA Attributes - Filter (NOT)'[Attribute] ),
        ALL ( 'LSOA Master')
    )
)

Counts the number of attributes the item has on the INCLUDE and EXCLUDE tables, and compares that to the inclusion criteria to return a True/False (If True, then the item meets the selected criteria)

ANDORNOT Logic =
VAR ANDORCondition = [AndOrLogicSwitch]
VAR NOTCondition = [AndOrLogicSwitch (NOT)]
VAR Include =
    and(

// This logic determines if the item meets the INCLUDE criteria
        or(not(isfiltered('LSOA Attributes - Filter'[Attribute])),DISTINCTCOUNT ( 'LSOA Attributes - Filter'[Attribute]) >= ANDORCondition)

// This logic determines if the item meets the EXCLUDE criteria
        , or(not(isfiltered('LSOA Attributes - Filter (NOT)'[Attribute])),DISTINCTCOUNT ( 'LSOA Attributes - Filter (NOT)'[Attribute] ) < NOTCondition))

RETURN
    Include

Provides a summary of how the filters have been applied

Filters applied = IF (
    ISFILTERED ( 'LSOA Attributes - Filter'[Attribute] ),
    IF (
        SUM ( ANDOR_Table[Binary] ) = 1,
        "INCLUDING items on at least one of the following registers: "
            & CONCATENATEX (
                VALUES ( 'LSOA Attributes - Filter'[Attribute]  ),
                'LSOA Attributes - Filter'[Attribute] ,
                " OR "
            ),
        "INCLUDING items on ALL of the following registers: "
            & CONCATENATEX (
                VALUES ( 'LSOA Attributes - Filter'[Attribute]  ),
                'LSOA Attributes - Filter'[Attribute] ,
                ", "
            )
    ),
    ""
)
    & UNICHAR ( 10 ) & IF (
        ISFILTERED ( 'LSOA Attributes - Filter (NOT)'[Attribute] ),
        IF (
            SUM ( 'ANDOR_Table (NOT)'[Binary] ) = 1,
            "  EXCLUDING items on at least one of the following registers: "
                & CONCATENATEX (
                    VALUES (  'LSOA Attributes - Filter (NOT)'[Attribute] ),
                     'LSOA Attributes - Filter (NOT)'[Attribute],
                    " OR "
                ),
            "  EXCLUDING items on ALL of the following registers: "
                & CONCATENATEX (
                    VALUES (  'LSOA Attributes - Filter (NOT)'[Attribute] ),
                     'LSOA Attributes - Filter (NOT)'[Attribute],
                    ", "
                )
        ),
        ""
    )

This measure needs to be applied as a filter (LogicFilter=1) to any visuals that show row level detail from the master table, so that items that don’t meet the filter criteria are excluded.

LogicFilter = if([ANDORNOT Logic],1,0)

Step 5: Create report measures using the “ANDORNOT Logic” measure as a FILTER

The ANDORNOT Logic measure, when applied at a row level to the master table, determines whether or not the item should be included based on the various filter criteria that have been applied. Further measures can then be created similar to the example below, which counts the number of items in the master table that meet the filter criteria.

Count of LSOAs = countrows(filter('LSOA Master',[ANDORNOT Logic]))

For more complex, measures, such as summing or averaging associated facts, a structure like that shown below can be used;

Population count = CALCULATE(sum('Mid-2019 Persons'[All Ages]),filter('LSOA Master',[ANDORNOT Logic]))

 

Step 6: Create front end report

You can now add slicers from the Filter tables and the AND/OR tables to create the filter experience, as well as the filter summary measure which will show to users how the filters have been applied:

Hopefully this will be helpful to others, and I would be very grateful for any ideas on how to improve on what I have done, particularly to improve performance for larger data sets.

Thank you

Alex

Original post: Advanced filter experience: "AND", "OR" , "NOT" filter logic using slicer visuals and DAX!!