Calculate Filter with Max measure or Max without measure

[Lovebo01]

Hello all,

I don't understand a thing from Calculate and Filter combinate :

I have a small table with 2 columns :

Quarter	Value
1	25
2	45
3	78
4	14
1	12

I am trying to calculate the sum of value for the last quarter selected by user (with a slicer from the report).

So If the user select only the quarter 1, sum = 25 + 12 = 37

If the user select only the quarter 3, 2 & 1, sum = 78 etc.

So I am using this Dax function : 

Value Last Quarter = CALCULATE([sum value],

Filter('Test','Test'[Quarter]=MAX(Quarter)))

[Sum value] = sum(Value)

With this function it works very well BUT if i change the MAX(Quarter) by a measure like :

Value Last Quarter = CALCULATE([sum value],

Filter('Test','Test'[Quarter]=[Max quarter]))

[Max quarter] = MAX(Quarter) 

I have all the sum, Value Last Quarter = 174

Do you know why ?

Thank you,

Lovebo,

[amitchandak]

@Lovebo01 , not very clear. When you select slicer value you do not need to filter. It should filter automatically.

When you use MAX(Quarter) in filter; it will take display row context. In case you use a measure. It might end up taking MAX(Quarter) as the static max value.

[Lovebo01]

Yes but If I don't filter my data it will summarize all values and I don't want to have all the values summarized.

I just want the last quarter selected. So If I select on my slicer Quarter : 1,2,3

I want the sum of all value for Quarter 3 (the max)

I don't want the sum of all value for Quarter 1 + Quarter 2 + Quarter 3. -> That's what I have with the Max in a measure.

If the measure is a static value, why it sums all the value for all Quarter selected from the slicer ?

Thank you for your help,

Lovebo,

[amitchandak]

@Lovebo01 ,

Try like

Value Last Quarter =
var _max = MAXX(allselected('Test'),'Test'[Quarter])
return
CALCULATE([sum value],
Filter('Test','Test'[Quarter]=_max))

or

Value Last Quarter =
var _max = MAXX(allselected('Test'),'Test'[Quarter])
return
CALCULATE([sum value],
Filter(allselected('Test'),'Test'[Quarter]=_max))

[Lovebo01]

Thank you for your solution,

But I would like to know why we have different result depending on using measure or not, in this case.

More precisely, why this option with 2 measures doesn't work :

Value Last Quarter = CALCULATE([sum value],

Filter('Test','Test'[Quarter]=[Max quarter]))

[Max quarter] = MAX(Quarter) 

While this one work :

Value Last Quarter = CALCULATE([sum value],

Filter('Test','Test'[Quarter]=MAX(Quarter)))

Thank you,

Lovebo,