cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
snph1777
Resolver I
Resolver I

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

st22.GIF

 

Table 2: ReferenceTable

st11.GIF

 

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.

1 ACCEPTED SOLUTION
snph1777
Resolver I
Resolver I

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
                        )

View solution in original post

5 REPLIES 5
snph1777
Resolver I
Resolver I

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
                        )

View solution in original post

daxer-almighty
Solution Sage
Solution Sage

[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_

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

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

@daxer-almighty 

 

 

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors