URGENT - DAX - How to insert value based on below conditions

[tanny1234]

 I am required to add the value of column C based on the value of (column A & column B). The value has to be inserted based on below conditions -

First step -> Get VAR x = value of column[B] where Column [A] = "Mango"

Second step -> Get the value VAR y = value from column[C] where VALUE (column [B]) = x and column[A] ="Apple"

Third step -> insert y in column[C] where  Column [A] = "Mango" and (column [B]) = x 

Example in the below table 1st row and Column C should have 1993

2nd row column C should have 5555

3rd row column C should have 66666

[tanny1234]

@amitchandakThanks for looking into it. Column B will have unique values for each entry of Column A, for e.g. "1809" will only exist 2 times, 1 each for Mango and Apple. Does it help?

[v-rzhou-msft · Accepted solution]

Hi @tanny1234 

Here I have two workarounds to achieve your goal.

1. If your table looks like the screenshot, where the A columns with the same B values are arranged in the order of Mango and Apple, then you can try the Fill Up/Down function in the Power Query Editor.

My Sample is the same like yours.

Result is as below.

2. Try Group By in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZA7DoAgDEDv0tlBfkJHD+AJiIODcTHK/SdbIJqCr0OTl5cOjRGW7TpuGECFEWnBOkSYUzr3zylEk/3bYp9m5QhZhvBzNbuJkbH3vouL84yMiS4uLmRErYm2rg4ZERuijauzjkfUlmjr6pQ2lh+yPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", Int64.Type}, {"C", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"B"}, {{"Rows", each _, type table [A=nullable text, B=nullable number, C=nullable number]}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Rows", "Rows - Copy"),
    #"Expanded Rows - Copy" = Table.ExpandTableColumn(#"Duplicated Column", "Rows - Copy", {"C"}, {"Rows - Copy.C"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Rows - Copy", {"B", "Rows"}, {{"Max", each List.Max([#"Rows - Copy.C"]), type nullable number}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows1", "Rows", {"A"}, {"Rows.A"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"Rows.A", "B", "Max"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Rows.A", "A"}, {"Max", "C"}})
in
    #"Renamed Columns"

For reference: Grouping or summarizing rows

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@tanny1234 , I doubt this can work unless there is one more reference

First value Column [A] = "Mango" B can have any value from Min to Max, what to take?