Search code examples
powerbidaxpowerbi-desktoppowerpivotssas-tabular

Power BI - DAX - table variable - use column for further computation

I am using DAX language in Power BI Desktop.

I have a tricky situation where I am trying to use the column name generated from a table variable.

Table 1: SourceTable

enter image description here

Table 2: ReferenceTable

enter image description here

I need to develop a calculated column in SourceTable called EmploymentStatus, based on the corresponding column in ReferenceTable. But I need only the EmploymentStatus value from ReferenceTable, for the maximum InternalID for a given EmployeeEmail.

For example, for the email xyz.gmail.com in SourceTable, I need the EmploymentStatus (calculated column) as 'Active' from ReferenceTable, since 'Active' has the maximum of the two available InternalID values (17, 15).

I tried the following code (Calculated Column in SourceTable):

EmploymentStatus_SourceTable_CalculatedColumn =
VAR tabl1 =
    SUMMARIZE (
        ReferenceTable,
        ReferenceTable[EmployeeEmail],
        "MaxInteralID", MAX ( ReferenceTable[InternalID] )
    )
VAR tabl2 =
    FILTER (
        ReferenceTable,
        ReferenceTable[InternalID] IN VALUES ( tabl1[MaxInteralID] )
    )
VAR NewCol =
    LOOKUPVALUE (
        tabl2[EmploymentStatus],
        tabl2[EmployeeEmail], SourceTable[EmployeeEmail]
    )
RETURN
    NewCol

I realize that I cannot use the column generated from the table variable.

For example, tabl1[MaxInteralID], tabl2[EmployeeStatus], tabl2[EmployeeEmail] - are all invalid.

Any idea on how to handle this? You can even provide me with a solution that does not use variables at all. Am okay with any solution.

Solution

  • Similar to here, you can find the maximal ID for each email and look up the status for that ID.

    Table and column names shortened for readability:

    CalcCol =
VAR Email = Source[Email]
VAR MaxID = CALCULATE ( MAX ( Ref[ID] ), Ref[Email] = Email )
RETURN
    LOOKUPVALUE ( Ref[Status], Ref[Email], Email, Ref[ID], MaxID )