Forum Discussion
How to count indicators per classification based on the latest date (DAX)
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.
Use a “latest row per indicator” virtual table, then count indicators in that reduced set (so each Indicator-Key appears once).
Indicators (Latest) =
VAR LatestPerIndicator =
ADDCOLUMNS (
VALUES ( 'Table'[Indicator-Key] ),
"__LatestDate", CALCULATE ( MAX ( 'Table'[Date] ) )
)
VAR LatestRows =
TREATAS (
SELECTCOLUMNS ( LatestPerIndicator, "Indicator-Key", [Indicator-Key], "Date", [__LatestDate] ),
'Table'[Indicator-Key], 'Table'[Date]
)
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[Indicator-Key] ),
LatestRows
)
Put Classification on rows/columns and this measure in Values, it will count each indicator only once, based on its latest available Date in the current filter context.
Thank you. But all of the solutions provided here have the same problem. In a chart, the value without classification is one, and with classification it's another. When they should add up to the same thing.
