How to count indicators per classification based on the latest date (DAX)

Hi 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:

1. 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).

2. TREATAS: It takes this virtual list and applies it as a strict filter to the data model.

3. 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.

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.

Hi,

Based on the table that you have shown, please show the expected result.