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.

Alexis Olson · Accepted Answer · 2021-07-07 20:55:35Z

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 7, 2021 at 20:55

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3812887 Over a year ago

thanks very much, will get back shortly. appreciate your help

user3812887 Over a year ago

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 Over a year ago

the above comment has proper table and column names, for anyone who wants to refer later; the solution is what Alexis posted

user3812887 Over a year ago

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 Over a year ago

this code immediately above, is a solution with better performance

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related