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

Question

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

Table 2: ReferenceTable

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.

score 1 · Accepted Answer · answered Jul 07 '21 at 20:55

1

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 )

answered Jul 07 '21 at 20:55

Alexis Olson

38,724
7
42
64

thanks very much, will get back shortly. appreciate your help – user3812887 Jul 07 '21 at 21:13
EmploymentStatus_SourceTable_CalculatedColumn = VAR Email = SourceTable[EmployeeEmail] VAR MaxID = CALCULATE ( MAX(ReferenceTable[InternalID]), ReferenceTable[EmployeeEmail] = Email ) RETURN LOOKUPVALUE( ReferenceTable[EmploymentStatus], ReferenceTable[EmployeeEmail], Email, ReferenceTable[InternalID], MaxID ) – user3812887 Jul 07 '21 at 23:05
the above comment has proper table and column names, for anyone who wants to refer later; the solution is what Alexis posted – user3812887 Jul 07 '21 at 23:06
EmploymentStatus_SourceTable_CalculatedColumn = var CurrentEmail = SourceTable[EmployeeEmail] var Status_ = SELECTCOLUMNS( topn(1, filter( ReferenceTable, ReferenceTable[EmployeeEmail] = CurrentEmail ), ReferenceTable[InternalID], DESC ), "@Status", ReferenceTable[EmploymentStatus] ) return Status_ – user3812887 Jul 09 '21 at 16:54
this code immediately above, is a solution with better performance – user3812887 Jul 09 '21 at 16:56

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

1 Answers1