Forum Discussion

lo_p_ez's avatar
lo_p_ez
Icon for Advocate II rankAdvocate II
8 years ago

Optional FILTER in CALCULATE

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 excl...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
lo_p_ez
Icon for Advocate II rankAdvocate II
8 years ago

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