Search code examples
powerbidaxcalculated-columnsmeasure

PowerBI measure or calculated column to look at the last unitname based on actiondate grouped by eventidx

I am looking for a way to either create a measure or calculated column in PBI desktop to get the last unitname per event_idx based on the latest actiondate. Below is a picture with data attached.

enter image description here

As you can see, there are many updates to a single event, so I would like to know the best way to take the last unitname based on the action date field and group it by event_idx. If this was sql I could do this easily but I am not as familiar with dax so I am struggling with the correct formulas and syntax.

Any help would be appreciated.

Thanks!!

Solution

  • If you need the measure to display the result only in relevant rows, then use this

    Measure1 =
CALCULATE (
    MAX ( tbl[UNITNAME] ),
    FILTER (
        tbl,
        tbl[ACTIONDATE]
            = CALCULATE ( MAX ( tbl[ACTIONDATE] ), ALLEXCEPT ( tbl, tbl[EVENT_IDX] ) )
    )
)

    If you want the measure to display the result per idx, then use this

    Measure2 =
VAR _idx =
    MAX ( tbl[EVENT_IDX] )
RETURN
    CALCULATE (
        MAX ( tbl[UNITNAME] ),
        FILTER (
            ALL ( tbl ),
            tbl[EVENT_IDX] = _idx
                && tbl[ACTIONDATE]
                    = CALCULATE ( MAX ( tbl[ACTIONDATE] ), ALLEXCEPT ( tbl, tbl[EVENT_IDX] ) )
        )
    )

    s1