Forum Discussion

Vincem35's avatar
Vincem35
Helper I
5 years ago
Solved

Exclude a Filter in a Calculated Table

Dear Community, 


I am working with a transaction table and would like to count the number of product categories a client purchase within a time frame.
results below are correct 

In the same page as this measure, i also have a bar chart displaying all the product category - see below

 

The issue i face is when i click on one product category - naturally the filter is propagated in the all table, as a result, i am getting 100% in Mono-Category although some clients have purchased this product category with another one

 

Below is my DAX

Thanks for your help!

 

Multi_Cat by clients = 
var Min_Date = [First Date Period TY]-- ie 2019-01-01
Var Max_Date = [End Date Period TY] -- ie 2019-12-31
Var Multi_Cat = values('Multi-Categories'[Column1]) -– Values from 1 to 4

VAR Customers_List =
    CALCULATETABLE (                        
        SUMMARIZE(' POS Boutique Transactions',' POS Boutique Transactions'[CLIENT_ID]),
        filter(' POS Boutique Transactions', ' POS Boutique Transactions'[DT_DOCUMENT]>= Min_Date && ' POS Boutique Transactions'[DT_DOCUMENT] <= Max_Date),
        filter(' POS Boutique Transactions', ' POS Boutique Transactions'[DT_VALID_TO] > Max_Date),
        ' POS Boutique Transactions'[SALESFORCE]= TRUE())

Var Table_Temp = 
        CALCULATETABLE(
        SUMMARIZE(' POS Boutique Transactions', ' POS Boutique Transactions'[CLIENT_ID],
        "Multi-Cat", DISTINCTCOUNT(' POS Boutique Transactions'[Product Category])),
        TREATAS (Customers_List,' POS Boutique Transactions'[CLIENT_ID]),
        filter(' POS Boutique Transactions', ' POS Boutique Transactions'[DT_DOCUMENT]>= Min_Date && ' POS Boutique Transactions'[DT_DOCUMENT] <= Max_Date),
        filter(' POS Boutique Transactions', ' POS Boutique Transactions'[DT_VALID_TO] > Max_Date))

Var Calc = 
    if(Multi_Cat = 1,
    CALCULATE(DISTINCTCOUNT(' POS Boutique Transactions'[CLIENT_ID]),
    filter(Table_Temp, [Multi-Cat] =Multi_Cat)),
    CALCULATE(DISTINCTCOUNT(' POS Boutique Transactions'[CLIENT_ID]),
    filter(Table_Temp, [Multi-Cat] >=Multi_Cat)))
    
Var Test = if(isblank(calc), 0, calc)
RETURN Test

 

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Vincem35,

    As mahoneypat said, you can use the 'edit interaction' panel to ignore the external filter effect to break the current visualization graph.

    Since current Dax formulas not able to extract multiple values from the slicer and filters who used the same data source table field. So if you want to keep the filter effect and ignore a specific filter, you can try to create a calculated table to duplicate the raw table field that you used on the filter. Then you can add conditions into the DAX expressions to compare with both two filter selections.

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Vincem35,

    As mahoneypat said, you can use the 'edit interaction' panel to ignore the external filter effect to break the current visualization graph.

    Since current Dax formulas not able to extract multiple values from the slicer and filters who used the same data source table field. So if you want to keep the filter effect and ignore a specific filter, you can try to create a calculated table to duplicate the raw table field that you used on the filter. Then you can add conditions into the DAX expressions to compare with both two filter selections.

    Regards,

    Xiaoxin Sheng