Forum Discussion
How to count indicators per classification based on the latest date (DAX)
- 7 months ago
Hello,
I think the issue is that LASTDATE(Table[Date]) returns only the last date in the current filter context, so indicators that have multiple months in the same quarter are still counted multiple times
what you need instead is to first identify, for each Indicator-Key, its own latest available date, and then count indicators only on those rowsyou can do it like this:
Indicators Latest := CALCULATE( DISTINCTCOUNT(Table[Indicator-Key]), FILTER( Table, Table[Date] = CALCULATE( MAX(Table[Date]), ALLEXCEPT(Table, Table[Indicator-Key]) ) ) )This should works because, for every indicator, it keeps only the row where the date is the maximum for that indicator, and all earlier months are ignored,
when you put Classification on rows, the indicator is counted only once, under its latest classification.
Hello,
I think the issue is that LASTDATE(Table[Date]) returns only the last date in the current filter context, so indicators that have multiple months in the same quarter are still counted multiple times
what you need instead is to first identify, for each Indicator-Key, its own latest available date, and then count indicators only on those rows
you can do it like this:
Indicators Latest :=
CALCULATE(
DISTINCTCOUNT(Table[Indicator-Key]),
FILTER(
Table,
Table[Date]
= CALCULATE(
MAX(Table[Date]),
ALLEXCEPT(Table, Table[Indicator-Key])
)
)
)This should works because, for every indicator, it keeps only the row where the date is the maximum for that indicator, and all earlier months are ignored,
when you put Classification on rows, the indicator is counted only once, under its latest classification.
Although i ended up using the summarize function to generate a new table with just the latest results, your hint about why lastdate was not correct pointed me the right way. So i've marked your answer as a solution. Thanks.