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

[snph1777]

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 CalculatedColumn 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.

[snph1777 · Accepted solution]

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
                        )

[daxer-almighty]

[Current Status] = // calculated column in SourceTable
var CurrentEmail = SourceTable[EmployeeEmail]
var Status_ =
    SELECTCOLUMNS(
        topn(1,
            filter(
                ReferenceTable,
                ReferenceTable[EmployeeEmail] = CurrentEmail
            ),
            ReferenceTable[InternalID],
            DESC
        ),
        "@Status",
            ReferenceTable[EmploymentStatus]
    )
return
    Status_

[snph1777]

Thanks very much for your suggestion. I have figured out using a simpler approach. Appreciate your help

[daxer-almighty]

@snph1777 

Sorry to say that but your code could bring the whole model to a halt when refreshing. The rule is one should not use CALCULATE when creating calculated columns. The main reason for this is that CALCULATE performs a very expensive operation called "context transition" and if you happen to execute this in a big table, you may not live to the end of the processing, figuratively speaking. Good advice from a soldier in the trenches: use my code that does not use CALCULATE.

[snph1777]

@daxer-almighty 

Thanks for the solution. I have used yours in my final Power BI report.