Use max value regardless of other field

[chaorenzhu]

Hi experts,

To simply, let's say we use such data

load * inline [
year, category, number
2021,A,20
2020,A,30
2020,B,40
];

In filter pane I use 2 dimensions, year and category. Now I create a KPI with the expression of 

If(getselectedcount(year)=0, sum({<year={"$(=max(year))"}>} number) ,sum(number))

to get the sum of number, with default value as the max of year.

However, when the user selects category as 'B' in the filter pane, because there's no record of category B in 2021, this expression will return 40. The user wants to show 0 instead, as the default year is expected to be the latest year (2021 in this case). 

Can you advise how to achieve it? Thank you!

[Vegar · Accepted solution]

You can add a set into your max() modifier like this.

If(getselectedcount(year)=0, sum({<year={"$(=max({1}year))"}>} number) ,sum(number))

[chaorenzhu]

Thanks Vegar, I should've thought of that