Forum Discussion

snph1777's avatar
snph1777
Icon for Helper V rankHelper V
5 years ago
Solved

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   Table 2: ReferenceTab...
Need Help
  • snph1777's avatar
    snph1777
    5 years 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
                            )

daxer-almighty's avatar
daxer-almighty
Icon for Solution Sage rankSolution Sage
5 years ago

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's avatar
snph1777
Icon for Helper V rankHelper V
5 years ago

daxer-almighty 

 

 

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