PowerBI CALCULATETABLE, FILTER with SELECTEDVALUE

user_85952 0
Viewed 3135

Am creating a table from original with filter condition, where my filter value is from SELECTEDVALUE

enter image description here

Table is not getting filtered on SELECTEDVALUE, if I replace with a real value then it works.

Code (doesn't work)

TransGt5 = 
var seletectedQuanity = SELECTEDVALUE(QuantityFilter[Quantity])
    return CALCULATETABLE(
        Transactions,
        FILTER(
                ALL(Transactions),
                Transactions[Quantity] >= seletectedQuanity
            ))

Code works file with hard coded value:

TransGt5 = 
var seletectedQuanity = SELECTEDVALUE(QuantityFilter[Quantity])
    return CALCULATETABLE(
        Transactions,
        FILTER(
                ALL(Transactions),
                Transactions[Quantity] >= 3
            ))

what am doing wrong?

dax powerbi
1 Answers

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.

For example, it could be something like:

Example =
VAR seletectedQuanity = SELECTEDVALUE ( QuantityFilter[Quantity] )

VAR FilteredTable =
    CALCULATETABLE (
        Transactions,
        Transactions[Quantity] >= seletectedQuanity )

VAR Result = SUMX ( FilteredTable, Transactions[Quantity] )

RETURN Result

(although for this example, there are easier ways to achieve the same results, without the calculated table)

user_36791470
Related