Dynamically generate calculate filter conditions

[bhalicki]

Hi all,

I have a scenario where I want to filter a table based on options selected from a disconnected table.

Products table:

Filter options table (which are added to a slicer):

I have a measure which evaulates the selected option and returns TRUE or FALSE for the product displayed in a table, which is then applied as a filter to either display the row or hide it.

ApplyFilter = 

VAR SelectedOptions = CONCATENATEX(VALUES('Option'[OptionId]), [OptionId], ",")

VAR FilterOption1 = IF(
                        ISFILTERED('Option') && CONTAINSROW(VALUES('Option'[OptionText]), "Show only bikes"), 
                                IF(SELECTEDVALUE('Product'[Type])="Bike", TRUE(), FALSE()), /* Only return TRUE if selected product is a bike */
                                TRUE()                                                      /* Always return true */
                    )

VAR FilterOption2 = IF(
                            ISFILTERED('Option') && CONTAINSROW(VALUES('Option'[OptionText]), "Show only red products"), 
                                IF(SELECTEDVALUE('Product'[Colour])="Red", TRUE(), FALSE()), /* Only return TRUE if selected colour is a red */
                                TRUE()
                    )

RETURN FilterOption1 && FilterOption2   /* Return TRUE if both FilterOption1 and FilterOption2 are both TRUE */

This gives me what I want, but I also need the ability to count the number of rows (using COUNTROWS), so that the filter can be applied in charts or other visualisations.

I have the following measure, but performance is terrible, so I was hoping there would be some way of dynamically generating the CALCULATE filter arguments, without generating a specific CALCULATE option for each scenario.

RowCount = CALCULATE(COUNTROWS('Product'), FILTER('Product', [ApplyFilter])) 

The actual scenario I am working on is significantly more complex (has up to 6 conditions, or 64 different combinations) which is why I want to avoid a separate calculate statement for each combination.

Kind regards,

Ben
(please no ChatGPT responses :))

[OwenAuger · Accepted solution]

Hi @bhalicki 

From your example, it appears that the logic is:

1. If the Option table is not filtered, then apply no filter.
2. If the Option table is filtered to one or more options, determine the set of Products corresponding to each selected option and take the intersection of those sets.
   For example, if "Show only bikes" and "Show only red products" are both selected, the resulting filter should correspond to "Red bikes".

I would recommend this general method:

1. Define the relationship between Options and Products by loading an additional table 'Option Product' to the model, containing the combinations. There is no need to determine these relationships on the fly since they are defined in advance:

2. Create relationships as follows, so that Option filters 'Option Product', which filters Product.

3. Create a calculation group with a calculation item that uses the method presented in this SQLBI article to apply the required filter to Product for any measure. In my sample PBIX, I created a calculation item "Products satisfying all options" with this expression:

IF (
    ISFILTERED ( Option ),
    VAR NumSelectedOptions = COUNTROWS ( Option )
    VAR OptionProducts =
        SUMMARIZE (
            'Option Product',
            Option[OptionId],
            Product[Product]
        )
    VAR ProductWithNumOptions =
        GROUPBY (
            OptionProducts,
            Product[Product],
            "@Options", SUMX ( CURRENTGROUP ( ), 1 )
        )
    VAR ProductWithAllOptions =
        FILTER (
            ProductWithNumOptions,
            [@Options] = NumSelectedOptions
        )
    VAR Result =
        CALCULATE ( SELECTEDMEASURE ( ), ProductWithAllOptions )
    RETURN
        Result,
    SELECTEDMEASURE ( )
)

This works by determining how many options are satisfied by each product, then applies a filter to include only those products that satisfy all selected conditions.

4. Whenever you want to apply the option-based filter, apply a filter corresponding to the above calculation item.

5. To demonstrate, I added some dummy sales data based on the 5 products. The lower card and the column chart each have the calulation item applied as a visual-level filter.

Does this method work for you in your actual model?

[bhalicki]

Thanks @OwenAuger , appreciate your time to provide a detailed solution 🙂

[danextian]

It’s honestly difficult to provide a reliable solution without understanding the full logic of what you’re trying to achieve. Also, please provide a sample data that we can easily copy-paste to Excel because who wants to be manually typing it?

[bhalicki]

Hi @danextian ,

Good point, thanks for pointing that out.

The data is:
Options table:

1	Show only bikes
2	Show only red products

Product table:

1	Car	Red
2	Bike	Red
3	Bike	Blue
4	Car	Green
5	Truck	Yellow

Unforunately I can't provide the specific scenario, but the one I have used above is closely modelled on what I want to achieve.

Essentially I want to be able to generate a measure similar to:

RowCount= CALCULATE(COUNTROWS('Product'),

                    IF(FilterOption1=TRUE(), 'Product'[Type]="Bike", ALL(Product[Type])))