Comparing rows - Need to match column values between rows as criteria for new column

[jlankford]

Hi, 

I have a table with three columns (BRAND, LOCATION, VOLUME).

Below is a table that explains what I'm trying to do:

So in this example, If "PRIVATE LABEL" exists in the same location as "OUR PRIVATE LABEL" I need "PRIVATE LABEL" to return a 0, but I'd like to keep the volumes of anything else that doesn't satisfy these requirements. 

I'm struggling with lookupvalue and I'm unsure of how to tackle this. 

Thanks

[dax]

Hi @jlankford , 

You could try to use below M code to see whether it work or not.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClDSUQoHYkMDA6VYHQjfA4iNkPjeIHlTCN8Rqh4m7whVD5X2R5hnihAAKTA0gQg4oRngBLUAaoAzzD6QdCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Brand = _t, Location = _t, vol = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Location", type text}, {"vol", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Location"}, {{"combine", each Text.Combine([Brand], ","), type text}, {"all", each _, type table [Brand=text, Location=text, vol=number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Brand", "vol"}, {"Brand", "vol"}),
    Custom1 = Table.ReplaceValue(#"Expanded all", each [combine] , each if Text.Contains([combine],"OP") and [Brand]="P" then 0 else [vol], Replacer.ReplaceValue, {"combine"}),
    #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"combine", Int64.Type}})
in
    #"Changed Type1"

Best Regards,
Zoe Zhi

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

[amitchandak · Accepted solution]

Try as a new column

if(isblank(
maxx(filter(table,table[location] =earlier([location]) && table[Brand] = "PRIVATE LABEL"
 && earlier(table[brand])="OUR PRIVATE LABEL"),table[VOULME])), table[VOULME],0)

Might have to  do some changes.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[jlankford]

Thanks goes to you and @dax  for providing great solutions! I had to reverse a few things to get it working, but I'm validating everything now, and it seems to be good. 

@dax : I really like your solution, and would generally prefer it, however, my dataset has more columns than I displayed above, and I had a little difficulty getting it all in there (probably making a typo on my end). Thank you for introducing me to grouping - It looks like cleaning my data and getting it the way I want it to is taking on a whole new dimension now - this is great!