Forum Discussion
Aggregation and dynamic filter
Hello,
I am trying to calculate aggregated data (by Level 1) which respond to slicer selections (on level 2)
Specific Example:
Data
| Level1 | Level2 | Level3 | Data |
| A | z | q | 1 |
| A | zz | q | 2 |
| A | zzz | r | 3 |
| A | zzzz | r | 4 |
| B | z | q | 10 |
| B | zz | q | 20 |
| B | zzz | r | 30 |
| B | zzzz | r | 40 |
Aggregate Data:
Option 1. Create a table using GroupBy and join it using Level1 (in the relationship tab)
Table1_agg = GROUPBY( Table1, Table1[Level1], "AggL1_Data",sumx(CURRENTGROUP(),Table1[Data]) )
Option 2. Use Filter wihtin Table1
AggL1_Filter =
Calculate(
sum(Table1[Data]),
FILTER(
Table1,
Table1[Level1]= EARLIER(Table1[Level1])
)
) Result - aggregation WORKS
However filtering using the Level2 Slicer does NOT work.
AggL1 still shows 10 and 100 where as I want to see 5 (for A) and 50 (for B)
Help would be greatly appreciated, I've been stuck on this for ages,
Thanks in advance,
dusiod
Ok I think I've made this work using:
0Measure = CALCULATE( SUM(Table1[Data]), FILTER(ALLSELECTED(Table1),Table1[Level1]=MAX(Table1[Level1])) )But can anyone explain what MAX(Table1[level1]) is doing in the filter.... do not understand especially as MAX is supposed to ignore Booleans and Text, and in this case Level1 has string fields....
dusiod,
Calculated columns will not respond to slicer selection, this is why your first DAX doesn't work with Level 2 filter. See this Power BI KB.
You eventually create a measure, which works correctly with level 2 filter. The max() function in the measure has similar effect as the EARLIER() function, it will check if your rows share same level1 value, and will result in your measure to sum data based on same level1.
Regards,
Lydia
2 Replies
dusiod,
Calculated columns will not respond to slicer selection, this is why your first DAX doesn't work with Level 2 filter. See this Power BI KB.
You eventually create a measure, which works correctly with level 2 filter. The max() function in the measure has similar effect as the EARLIER() function, it will check if your rows share same level1 value, and will result in your measure to sum data based on same level1.
Regards,
LydiaOk I think I've made this work using:
0Measure = CALCULATE( SUM(Table1[Data]), FILTER(ALLSELECTED(Table1),Table1[Level1]=MAX(Table1[Level1])) )But can anyone explain what MAX(Table1[level1]) is doing in the filter.... do not understand especially as MAX is supposed to ignore Booleans and Text, and in this case Level1 has string fields....