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

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
                            )

5 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago
    [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's avatar
      snph1777
      Icon for Helper V rankHelper V
      5 years ago

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

      • 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

    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
                            )