Optional FILTER in CALCULATE

[lo_p_ez]

Similarly to the Basket Analysis  DAX pattern model, I have 1 fact for Sales, 1 dimension for Product and an extra dimension for Filter Product.

I want to use the Filter Product dimension to exclude products chosen by the user. I made it work with this DAX formula:

Sales =
CALCULATE (
    SUM ( Sales['Sales'] ),
    FILTER (
        Product,
        NOT ( 'Product'['ProductName'] IN VALUES ( 'FilterProduct'['ProductName'] ) )
    )
)

This works as long as the user has already chosen a Product to exclude on FilterProduct slicer. But if nothing has been selected, the calculation will show blank, rather than just show everything. I wonder if there's a way to handle this gracefully. An idea I had was to create a variable and see if FilterProduct ISFILTERED(). If so, copy&paste the above with the FILTER() on  SWITCH statement, if not, just skip the FILTER(). But this isn't great, because it duplicates code, and if I was to add another optional filter (e.g. SalesRegion), I'd had to pre-calculate all the combinations (e.g. SalesRegion & Product, just SalesRegion, just Product, none).

[v-jiascu-msft]

Hi @lo_p_ez,

Based on your description, please try out the function VAR like this.

Sales =
VAR temp =
    CALCULATE (
        SUM ( Sales['Sales'] ),
        FILTER (
            Product,
            NOT ( 'Product'['ProductName'] IN VALUES ( 'FilterProduct'['ProductName'] ) )
        )
    )
RETURN
    IF ( ISFILTERED ( 'table'[SalesRegion] ), temp, 0 )

Best Regards,

Dale

[lo_p_ez]

Thanks @v-jiascu-msft

Maybe I didn't express it well. The problem here is that if the VALUES() doesn't return anything  (i.e. the user didn't pick any item to exclude) the whole calculation breaks.

Also, the reason why I thought of optional filter arguments is to avoid having to handle every possible combination. If you have only 1 filter to exclude you can handle this by building 2 calculation variables: 1 with the filter for when FilterProduct ISFILTERED(); and 1 without the exclusion filter.

However, if you have 2 filters to exclude (e.g. FilterProduct and FilterRegion) then you have to build 4 calculation varibles. 1 with no filter, 1 with just filter on FilterProduct, 1 with just filter on FilterRegion, 1 with both filters. This doesn't scale very well...