Exclude a Filter in a Calculated Table

[Vincem35]

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 · Accepted solution]

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

[mahoneypat]

Have you already used the edit interactions feature to prevent the undesired cross filter behavior?

Change how visuals interact in a report - Power BI | Microsoft Docs

Regards,

Pat