Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Iterating over Measure calculations, counting current status of groups.

Hi, is there a better way of doing this?   Lets say I have to identify the current application stage of candidates in our system. To do so, I'll first flag candidates with an IF-statement that eval...
  • AlexisOlson's avatar
    4 years ago

    It's fairly trivial to combine the measures you have into one by expanding [Flag] inside [Total] but I'm guessing you're looking for something cleaner.

     

    I propose the following:

     

    StatusCount =
    VAR Summary =
        SUMMARIZE ( ALL ( Data ), Data[Applicant ID], "MaxDate", MAX ( Data[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Data ),
            TREATAS ( Summary, Data[Applicant ID], Data[Date] )
        )

     

    First, we compute a Summary table to get the maximal date for each distinct applicant. Note that I've used ALL since, otherwise, we'd only get the rows corresponding to the current Application Status filter.

    Bearing this in mind, inside of COUNTROWS, Data is a subtable already filtered on Application Status but we wish to filter it further by only considering the most recent status.

    To do this, I use TREATAS to treat the [Applicant ID] and [MaxDate] columns of Summarize as the only set pairs allowable for Data[Applicant ID] and Data[Date].

     

    This simple-looking measure hides quite a bit of subtlety. I recommend reading this article to get a better handle on this sort of pattern:
    https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/