Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
)
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
)
[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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |