0

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?

RADO
  • 7,733
  • 3
  • 19
  • 33
Greg Pagendam-Turner
  • 2,356
  • 5
  • 32
  • 49

2 Answers2

1

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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
0

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")

Max
  • 337
  • 1
  • 10
  • How do I get the derived table to honour the filter? I want to generate a series from the sum. GENERATESERIES(1, MySum, 1) Need this to create a list of numbers from one to the filtered sum. – Greg Pagendam-Turner Nov 19 '21 at 08:35
  • What exactly is your goal for the list of numbers? Isn't is possible with a measure with filters? Or perhaps a calculated column? Or in Power Query you can reference your original table, making a duplicate, where you can delete rows / columns and filter, without changing the original. – Max Nov 19 '21 at 08:49
  • I have a number of filters. One will be the unit and the other a date range. I want the list of numbers to be thresholds. For this example I want to get something link in table a edit above. – Greg Pagendam-Turner Nov 19 '21 at 08:51
  • Would GENERATESERIES(1, CALCULATE(SUM('table1'[value]),'table1'[Unit] = "U1"), 1) help you? – Max Nov 19 '21 at 08:54
  • I want to use Units selected on the filter. Might be more than one unit selected. – Greg Pagendam-Turner Nov 19 '21 at 08:56
  • https://stackoverflow.com/questions/66169358/how-can-i-generate-a-dynamic-series-in-power-bi-for-which-the-first-value-depen will this post help you out a bit? Maybe GENERATESERIES in combination with a measure? – Max Nov 19 '21 at 09:06
  • Thanks but that example doesn't use GENERATESERIES – Greg Pagendam-Turner Nov 19 '21 at 09:14
  • I've tried out using generatieseries in combination with a measure, but to no avail. I can't figure it out either but any documentation on this suggest to use pure measures (Since that is also the way to make it dynamic in Power BI). Could you tell me what the exact business goal is for the series based of unit, date and unitvalue as max? Perhaps if I clearly know the goal I can help you further. (Or others reading this) – Max Nov 19 '21 at 09:25
  • Really appreciate your attention on this Max. We want to know if the selected units meet their operating thresholds. Each unit will have a target value for a particular day and an actual value. It's kind of like those strength meters at the fair. We want to measure which thresholds are passed by the actual amount. The target will be the max height of the test. – Greg Pagendam-Turner Nov 19 '21 at 09:32
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/239388/discussion-between-max-and-greg-pagendam-turner). – Max Nov 19 '21 at 09:37