CODEWITHSUNDEEP
powerbidax
Greg Pagendam-Turner
Greg Pagendam-Turner

Reputation: 2532

Why doesn't Table Constuctor honour the filter context?

I have a PowerBI Dashboard.

Table1

Unit Value
U1 20
U2 60

I have a filter on Unit = U1

I have a measure that sums up the value field.

MySum = sum('Table1'[value])

If I put the result in a panel I get: 20

If I create a derived table:

Table2 = { sum('Table1'[value]) }

and then put the result in a panel I get: 80

Why doesn't the second calc honour the filter?

Upvotes: 0

Views: 59

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

A calculated table cannot be responsive to filter context. This is because calculated columns and calculated tables are computed only when the data model is first loaded or refreshed rather than dynamically in response to the slicers and filters.

Measures are the only way to go for dynamic calculations. Note that measures can include dynamically defined tables within their definition; they just can't output anything other than a single value.

Upvotes: 1

user10471965
user10471965

Reputation:

That is because you specifically only filter table 1. In table 2, you don't specify what to take from table 1, thus it will take everything.

That means that once you've put a filter in place for U1 you will get: table 1 - sum('Table1'[value]) = 20 table 2 - sum('Table1'[value]) = 80

In this case it would be better to use measures instead of tables. In the measure you can then specify what parameter to filter on, resulting in for example: SumValue = SUM('table1'[value]) SumValue_U1 = CALCULATE(SUM('table1'[value]),'table1'[Unit] = "U1")

Upvotes: 0

Related Questions