Create Column in DAX that references other values similar to vlookup

[campbellmurphy]

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

[Icey · Accepted 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.

[campbellmurphy]

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

[nandic]

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:

UNION Table =
ADDCOLUMNS(
UNION(SUMMARIZE('TABLE C: Index2','TABLE C: Index2'[Code (PK)], 'TABLE C: Index2'[Code (FK)],'TABLE C: Index2'[KPI Number],'TABLE C: Index2'[KPI Name],'TABLE C: Index2'[Department],'TABLE C: Index2'[Country],'TABLE C: Index2'[Balance],'TABLE C: Index2'[Period],'TABLE C: Index2'[Operator],'TABLE C: Index2'[A],'TABLE C: Index2'[B]), SUMMARIZE('GENERATE Table','GENERATE Table'[Code (PK)], 'GENERATE Table'[Code (FK)],'GENERATE Table'[KPI Number],'GENERATE Table'[KPI Name], 'GENERATE Table'[Department], 'GENERATE Table'[Country], 'GENERATE Table'[Balance], 'GENERATE Table'[Period], 'GENERATE Table'[Operator], 'GENERATE Table'[A], 'GENERATE Table'[B])),
"CustomCalc",

  SWITCH('TABLE C: Index2'[Operator],

     "/",'TABLE C: Index2'[A]/'TABLE C: Index2'[B],

     "+",'TABLE C: Index2'[A]+'TABLE C: Index2'[B],

     "Formula2",('TABLE C: Index2'[A]+'TABLE C: Index2'[B])/'TABLE C: Index2'[B]

  )

)

This way, you use existing table and create new calculation column based on formula sign or text.

Regards.
Nemanja Andic

[campbellmurphy]

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.

• KPI 635 the answer is 7800/2600 = 3.  This is the Balance of KPI 1 divided by the Balance of KPI 3
• KPI 2393 the answer is 2500 - 2700 = -200. This is the Balance of KPI 2 minus the Balance of KPI 4

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