Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have a question would like to ask. Here is my data model
Item Key | DateYear |
111111 | 2020 |
111111 | 2019 |
111111 | 2017 |
222222 | 2020 |
222222 | 2015 |
222222 | 2014 |
333333 | 2020 |
333333 | 2018 |
333333 | 2014 |
I would like to creating a expression to count if the selected year is the minimum year for that Item key
For example, if I select Fiscal Year 2014, the count would be 2, because 2014 is the minimum year of 222222 and 333333.
If I select 2017, the count would be 1 because 111111 has the minimum year of 2017.
I was thinking something like
Count({<[DateYear] = {"$(=aggr(min([DateYear]), distinct [Item Key]))"}>} [Item Key])
But then I realize this will not work, can I get some help on this? Thanks guys.
@Qiyanlu66 where you want to use this expression? In chat? If yes, what will be the dimension?
@Qiyanlu66 meanwhile try below expression
=count(distinct aggr(if(DateYear = min(total <[Item Key]>DateYear),[Item Key]),[Item Key],DateYear))
Hi @Qiyanlu66
Alternate way to achieve the same result like below
=Sum(Aggr(If(DateYear = Min(TOTAL<[Item Key]> DateYear),1), [Item Key]))