CODEWITHSUNDEEP
excelpowerbidaxpowerpivot
Victor
Victor

Reputation: 1205

Optional FILTER in CALCULATE in DAX

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).

Upvotes: 2

Views: 646

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

I think you can use the ISFILTERED function, but not exactly how you were suggesting. Try inserting it into you measure like this:

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

Upvotes: 3

Related Questions