Forum Discussion

Prihatama's avatar
Prihatama
Frequent Visitor
2 years ago
Solved

Dynamic Filter on Calculatetable (Doesn't Work)

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 slice...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

saurabhtd's avatar
saurabhtd
Icon for Resolver II rankResolver II
2 years ago

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's avatar
    Prihatama
    Frequent Visitor
    2 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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