Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Everyone,
I would like to create a column using DAX that references other values similiar to a vlookup. I have been able to achieve the same result with a custom column in Power Query (see code below) however I need to be able to perform the same operation in DAX as I understand it's not possible to go from Power Query to create a new tables in DAX with GENERATE & UNION then back to Power Query.
Ideally the two columns to be created would be the "Workings" and "Operated Balance" columns with the functionality for custom formulas e.g. Formula2 for KPI 2374 which could be hardcoded at a later date, potentially using SWITCH
I've also created a Sample Power BI File for additional context.
Any assistance is greatly appreciated.
UNION Table with Custom Columns added (CC):
KPI Number | KPI Name | Department | Balance | Operator | A | B | CC Workings | CC Operated Balance |
1 | Total Sales | Overall | 7800 | 7800 | 7800 | |||
2 | Total Sales | Body Shop | 2500 | 2500 | 2500 | |||
2372 | Uncharged at effective rate | Body Shop | * | 3 | 4 | 2600*2700 | 7020000 | |
2373 | Total Income | Service | Formula1 | 2 | 3 | 2/3+1 | 1.666666667 | |
2374 | Total Sales | Service | Formula2 | 3 | 4 | (3+4)/4 | 1.75 | |
2393 | Unsold hours | Service | '- | 2 | 4 | 2500-2700 | '-200 | |
3 | Total Sales | Pre-Delivery | 2600 | 2600 | 2600 | |||
4 | Total Sales | New Vehicles | 2700 | 2700 | 2700 | |||
635 | Sales per advisor FTE | Body Shop | / | 1 | 3 | 7800/2600 | 3 | |
715 | Total Income | Administration | + | 2 | 3 | 2500+2600 | 5100 |
Current Power Query Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDRSsMwFIZf5dBL3XBN1lUvHTrwRoVOb8ouQnK2BtJmpFllb+9JuykmQkJP4P/6/UldZ3k2y7bWCwOVMNjT6W1AJ4yhqbxfLOhzWbtZnbEkvbbqDFVjjzSzIs7zJP/ucP6ERpPkHJBVjCwT5BW/4BMbLacjK2NkxQsaxzgc0YFQg+6tg832OWpI6452uDSfLsQfQsePrrdGQWNPLigqdIOWOAHz4KS9HIEyL34KvnTStiH1qFrd6d474bXtJuz2gl09JRs9shHugAqEB9zvUXp6CSAO06Y3I30V0x94av7TdGNdezIij83pk/6HsV/d7hs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"KPI Number" = _t, #"KPI Name" = _t, Department = _t, Balance = _t, Operator = _t, A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"KPI Number", type text}, {"KPI Name", type text}, {"Department", type text}, {"Balance", type text}, {"Operator", type text}, {"A", Int64.Type}, {"B", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try if [Operator]="Formula1"
then #"Changed Type"[Balance]{[A]-1} & "/" & #"Changed Type"[Balance]{[B]-1} & "+5"
else if [Operator]="Formula2"
then "(" & #"Changed Type"[Balance]{[A]-1} & "+" & #"Changed Type"[Balance]{[B]-1} & ")" & "/" & #"Changed Type"[Balance]{[B]-1}
else #"Changed Type"[Balance]{[A]-1} & [Operator] & #"Changed Type"[Balance]{[B]-1}
otherwise [Balance]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Workings"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Operated Balance", each Expression.Evaluate([Workings]))
in
#"Added Custom1"
Thanks
Solved! Go to Solution.
Hi @campbellmurphy ,
Try to create a calculated table and two calculated columns like so:
Table =
VAR KPI_Table =
SUMMARIZE (
ADDCOLUMNS (
'TABLE A: KPIs',
"Balance",
CALCULATE (
SUM ( 'TABLE B: Balances'[Balance] ),
FILTER (
'TABLE B: Balances',
SEARCH ( 'TABLE A: KPIs'[Code (PK)], 'TABLE B: Balances'[Code (FK)],, 0 )
)
)
),
[KPI Number],
[KPI Name],
[Department],
[Balance],
[Operator],
[A],
[B]
)
VAR Index2_Table =
SUMMARIZE (
'TABLE C: Index2',
[KPI Number],
[KPI Name],
[Department],
[Balance],
[Operator],
[A],
[B]
)
VAR Union_Table =
UNION ( KPI_Table, Index2_Table )
RETURN
Union_Table
Workings =
SWITCH (
[Operator],
"Formula1",
[A] & "/" & [B] & "+1",
"Formula2",
"(" & [A] & "+" & [B] & ")" & "/" & [B],
"", CONVERT ( [Balance], STRING ),
CALCULATE (
MAX ( [Balance] ),
FILTER ( 'Table', [KPI Number] = EARLIER ( [A] ) )
) & [Operator]
& CALCULATE (
MAX ( [Balance] ),
FILTER ( 'Table', [KPI Number] = EARLIER ( [B] ) )
)
)
Operated Balance =
VAR Balance_A =
CALCULATE (
MAX ( [Balance] ),
FILTER ( 'Table', [KPI Number] = EARLIER ( [A] ) )
)
VAR Balance_B =
CALCULATE (
MAX ( [Balance] ),
FILTER ( 'Table', [KPI Number] = EARLIER ( [B] ) )
)
RETURN
SWITCH (
[Operator],
"Formula1", DIVIDE ( [A], [B] ) + 1,
"Formula2", DIVIDE ( [A] + [B], [B] ),
"", [Balance],
"+", Balance_A + Balance_B,
"-", Balance_A - Balance_B,
"*", Balance_A * Balance_B,
"/", DIVIDE ( Balance_A, Balance_B )
)
Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it faster.
Hi @campbellmurphy ,
Try to create a calculated table and two calculated columns like so:
Table =
VAR KPI_Table =
SUMMARIZE (
ADDCOLUMNS (
'TABLE A: KPIs',
"Balance",
CALCULATE (
SUM ( 'TABLE B: Balances'[Balance] ),
FILTER (
'TABLE B: Balances',
SEARCH ( 'TABLE A: KPIs'[Code (PK)], 'TABLE B: Balances'[Code (FK)],, 0 )
)
)
),
[KPI Number],
[KPI Name],
[Department],
[Balance],
[Operator],
[A],
[B]
)
VAR Index2_Table =
SUMMARIZE (
'TABLE C: Index2',
[KPI Number],
[KPI Name],
[Department],
[Balance],
[Operator],
[A],
[B]
)
VAR Union_Table =
UNION ( KPI_Table, Index2_Table )
RETURN
Union_Table
Workings =
SWITCH (
[Operator],
"Formula1",
[A] & "/" & [B] & "+1",
"Formula2",
"(" & [A] & "+" & [B] & ")" & "/" & [B],
"", CONVERT ( [Balance], STRING ),
CALCULATE (
MAX ( [Balance] ),
FILTER ( 'Table', [KPI Number] = EARLIER ( [A] ) )
) & [Operator]
& CALCULATE (
MAX ( [Balance] ),
FILTER ( 'Table', [KPI Number] = EARLIER ( [B] ) )
)
)
Operated Balance =
VAR Balance_A =
CALCULATE (
MAX ( [Balance] ),
FILTER ( 'Table', [KPI Number] = EARLIER ( [A] ) )
)
VAR Balance_B =
CALCULATE (
MAX ( [Balance] ),
FILTER ( 'Table', [KPI Number] = EARLIER ( [B] ) )
)
RETURN
SWITCH (
[Operator],
"Formula1", DIVIDE ( [A], [B] ) + 1,
"Formula2", DIVIDE ( [A] + [B], [B] ),
"", [Balance],
"+", Balance_A + Balance_B,
"-", Balance_A - Balance_B,
"*", Balance_A * Balance_B,
"/", DIVIDE ( Balance_A, Balance_B )
)
Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it faster.
Your solution is beautiful and elegant and I think it will resolve the issue. I should be able to provide an additional update in around 16 hours. Apologies for the delay
Hi @campbellmurphy ,
Is there any option to add columns to existing UNION table?
Just add ADDCOLUMNS at the beginning, then existing code and after existing code add calculation for new column.
Example:
Hi @nandic
Thanks for the suggestion. I think you may be on the right path. I need the CustomCalc column to reference the Balance of other KPI's rather than taking the value from A and B directly e.g.
I've been able to do this in Power Query after some assistance in a previous post but am unsure how to do it in DAX. Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDRSsMwFIZf5dBL3XBN1lUvHTrwRoVOb8ouQnK2BtJmpFllb+9JuykmQkJP4P/6/UldZ3k2y7bWCwOVMNjT6W1AJ4yhqbxfLOhzWbtZnbEkvbbqDFVjjzSzIs7zJP/ucP6ERpPkHJBVjCwT5BW/4BMbLacjK2NkxQsaxzgc0YFQg+6tg832OWpI6452uDSfLsQfQsePrrdGQWNPLigqdIOWOAHz4KS9HIEyL34KvnTStiH1qFrd6d474bXtJuz2gl09JRs9shHugAqEB9zvUXp6CSAO06Y3I30V0x94av7TdGNdezIij83pk/6HsV/d7hs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"KPI Number" = _t, #"KPI Name" = _t, Department = _t, Balance = _t, Operator = _t, A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"KPI Number", type text}, {"KPI Name", type text}, {"Department", type text}, {"Balance", type text}, {"Operator", type text}, {"A", Int64.Type}, {"B", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try if [Operator]="Formula1"
then #"Changed Type"[Balance]{[A]-1} & "/" & #"Changed Type"[Balance]{[B]-1} & "+5"
else if [Operator]="Formula2"
then "(" & #"Changed Type"[Balance]{[A]-1} & "+" & #"Changed Type"[Balance]{[B]-1} & ")" & "/" & #"Changed Type"[Balance]{[B]-1}
else #"Changed Type"[Balance]{[A]-1} & [Operator] & #"Changed Type"[Balance]{[B]-1}
otherwise [Balance]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Workings"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Operated Balance", each Expression.Evaluate([Workings]))
in
#"Added Custom1"
Thanks,
Campbell