Aggregation and dynamic filter

[dusiod]

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

[dusiod · Accepted solution]

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

[v-yuezhe-msft · Accepted solution]

@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

[dusiod · Accepted solution]

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