Dynamic Filter on Calculatetable (Doesn't Work)

[Prihatama]

Hay Everyone,

I have a problem when implement dynamic filter on calculatetable.

Kindly please see below table.

I have 2 Table. Table A with item name and date (YYYYMM) & Table B for slicer.

I want combine 2 condition with EXCEPT function using filter by slicer.

Below is my DAX :

result = 

Var prev = CALCULATETABLE(VALUES(Table A[Item Name]), FILTER(Table A,Table A[Date]=Min(Table B[Date])))

Var curr = CALCULATETABLE(VALUES(Table A[Item Name]), FILTER(Table A,Table A[Date]=Max(Table B[Date])))

Var exp = EXCEPT(curr,prev)

return

exp

Actually it's work, but when i have changed the slicer then the result was same.

I have read from this forums, someone inform that dynamic filter doesn't work in calculatetable.

Anyone has idea for my case ? i just want to recognize the item in current month that not exist in previuos month.

thank you for any advice 😊

[saurabhtd]

@Prihatama 
That's not how Power BI works conceptually.

DAX can be used in 2 ways: to query data, and to define data (similar to SQL).

For queries, you can create DAX measures. They are executed in the run time and can respond to slicers and other user actions.

For calculated tables and columns, you can also write DAX code, but it's executed only in design time, when you create the code or refresh the data. It does not run as a query, and can not respond to user actions. The fact that you used DAX to create a table is irrelevant - the result is a static table, identical to the imported tables.

The only way to make it work is to build a measure. Inside measures, you can calculate tables, store them in variables, use them to calculate whatever you need and then publish a result. The result will be responsive to slicers. 

https://stackoverflow.com/a/70148616 
https://community.fabric.microsoft.com/t5/Desktop/Create-dynamic-tables-based-on-filters/m-p/261550 

[Prihatama]

@saurabhtd 

thank you for your advice.

But when i see the DAX, the return of result is quantity. 

What if the return is list of data ? Do you have any idea ?

Because i want show detail of data..

[v-shex-msft · Accepted solution]

HI @Prihatama,

I think you can consider use IN operator, it can be used to compare value and list.

The IN operator in DAX - SQLBI

BTW, if you want to use large operator on the list, I think you only need to check the max one. If the current value large than the max value, it should also be suitable for the each of value in the list.

formula =
VAR selected =
    VALUES ( QuantityTable[Quantity] )
VAR result =
    CALCULATE (
        SUM ( QuantityTable[Quantity] ),
        FILTER (
            ALLSELECTED ( QuantityTable ),
            QuantityTable[Quantity] >= MAXX ( selected, [Quantity] )
                || QuantityTable[Quantity] IN selected
        )
    )
RETURN
    result

Regards,

Xiaoxin Sheng

[Fowmy]

@Prihatama 

While creating a table or calculated column in Power BI, capturing slicer selections in real-time isn't feasible. Power BI constructs tables and columns before making them available on the report canvas. To assist you more effectively, kindly explain your requirements clearly, furnish sample data, and outline the expected outcome. This will enable a tailored solution to meet your specific needs. An approach using Measures should work in your case.

Based on your code I suggest you use a measure. Inside the measure you can create this particular table and use it as filters for the measure that you are calculating

[Prihatama]

@Fowmy 

thank you very much for your advice 😉