DAX help ! Lookup Value and get Latest Entry for specific Incident

I have 2 related tables in PowerBI (Incident) and (Mutation). The Incident table looks like this:

IncidentIDIncident NumberDate ResolvedByIDClosedByID
{123} I000123 01/01/2018DAX01 DAX02

The Mutation table looks like this:

ID StatusIDDate OperatorIDID_ResolvedID_Closed
{123} {OPE} 01/01/2018{JCK} DAX04 DAX05
{123} {RES} 02/01/2018{JNE} DAX04 DAX05
{123} {CLO} 03/01/2018{JON} DAX04 DAX05
{123} {REO} 03/01/2018{JNE} DAX04 DAX05
{123} {CLO} 04/01/2018{JCK} DAX04 DAX05
{124} {OPE} 01/01/2018{JNE} DAX04 DAX05

To Calculate the ID_Resolved and ID_Closed (DAX04 and DAX05) i use formula:

ID_Closed = IF(Mutation[statusID]="{CLO}";Mutation[ID];CONCATENATE(Mutation[ID];Mutation[statusID]))
ID_Resolved = IF(Mutation[statusID]="{RES}";Mutation[ID];CONCATENATE(Mutation[ID];Mutation[statusID]))

for Dax05 for example, every StatusID which is CLO now has value {123}, the other doesn't match the IncidentID and has value {123}{OTHERNAME}

Now to calculate the ResolvedByID (Dax01), because there is only 1 {RES} in the Mutation table, i can use:

ResolvedByID = LOOKUPVALUE(Mutation[operatorID];Mutation[ID_Resolved];Incident[incidentID])

For the ClosedByID things are more complicated, there are multiple {CLO} values. When i use the same formule as ResolvedByID i have an error message:

A Table of Multiple values was supplied. 

Now how can i get the value of the newest date in this cell ? {JCK}

I added my PowerBi File as reference

https://drive.google.com/open?id=1dygHLYMPYJS_d5qjWol2t7fswNCTuBmM

1 answer

  • answered 2018-01-11 20:43 Alexis Olson

    You can calculate the newest date and add it to your lookup:

    ClosedByID =
        VAR MaxDate = MAXX(FILTER(Mutation; Mutation[ID_Closed] = Incident[incidentID]);
                           Mutation[Date])
        RETURN LOOKUPVALUE(Mutation[operatorID];                
                           Mutation[ID_Closed]; Incident[incidentID];
                           Mutation[Date]; MaxDate)