Forum Discussion
Helper IVHow 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.
Super UserHi qmestu ,
The issue with your current measure is that LASTDATE evaluates based on the current context (e.g., the entire quarter), but it does not evaluate row-by-row for each indicator to find its specific latest date. This causes "semi-additive" issues where an indicator might be counted against multiple classifications if its status changed during the selected period.
To count each indicator only once,based on its latest status within the selected period,you need to identify the max date for each indicator first, and then filter the data to those specific rows.
Here is the most performant DAX pattern (using TREATAS) to achieve this:
The Solution: "Last Non-Empty" Pattern
Count Latest Classification =
VAR _MaxDatesPerIndicator =
ADDCOLUMNS(
VALUES('Table'[Indicator-Key]),
"LatestDate", CALCULATE(MAX('Table'[Date]))
)
RETURN
CALCULATE(
DISTINCTCOUNT('Table'[Indicator-Key]),
-- This effectively filters the table to keep ONLY the latest row for each indicator
TREATAS(_MaxDatesPerIndicator, 'Table'[Indicator-Key], 'Table'[Date])
)Why this works:
VAR _MaxDatesPerIndicator: It creates a virtual table in memory containing a list of every visible Indicator and its corresponding Max Date (e.g., IND-X7 | 01/03/2025).
TREATAS: It takes this virtual list and applies it as a strict filter to the data model.
Result: The calculation DISTINCTCOUNT now only sees the rows that match those latest dates.
If you put "Classification" in a visual (table/chart), an indicator will only appear under its classification from that latest date.
If an indicator changed from "Not Classified" (Jan) to "Exceeded" (Mar), this measure ensures it is only counted as "Exceeded".
Note on Performance: The solution suggested by @DanieleUgoCopp using FILTER(Table, ...) is logically correct, but the TREATAS approach above is significantly faster on larger datasets because it avoids iterating through the entire fact table row-by-row.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.
qmestuHi,
Thank you. How can i modify your expression, so that doesn't count rows where Indicator-Key has a blank value in another column (not pictured in the sample data)?
- amitchandak
qmestu , Try like
Calculate(lastnonblankvalue(Table[Date], DISTINCTCOUNT(Table[Indicator-Key])), not(isblank(Table[Indicator-Key])))
