Group by columns in DAX

[Anonymous]

Hi All,

I had an input table which looked like this

Anchor Dealer	Target Dealer	Revenue
Dealer1:Dealer2	Dealer2:Dealer3	124

And output table which has extra Compare value which will be either Yes or No for each combination of Anchor Dealer and Target Dealer. The issue after splitting Anchor Dealer and Target Dealer are that now I get 4 rows for 1 row of Revenue i.e revenue is counted 4 times in my Table

Anchor Dealer	Target Dealer	Compare Value	Revenue
Dealer1	Dealer2	Yes	124
Dealer1	Dealer3	Yes	124
Dealer2	Dealer2	Yes	124
Dealer2	Dealer3	Yes	124

I don't want to do a revenue/4 type of solution. Can you please guide on how to get the output like below:

Anchor Dealer	Target Dealer	Revenue	Compare Value
Dealer1:Dealer2	Dealer2:Dealer3	124	Yes

[JarroVGIT]

If this is your input table:

Anchor Dealer	Target Dealer	Revenue
Dealer1:Dealer2	Dealer2:Dealer3	124

And your desired table is this:

Anchor Dealer	Target Dealer	Revenue	Compare Value
Dealer1:Dealer2	Dealer2:Dealer3	124	Yes

You'll have to explain when the compare value is YES and when it should be NO?

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂

[Anonymous]

Yes, your assumptions about input and output table is correct.

Your question on how compare value is derived:

There is another column called F value which compares anchor dealer and target dealer.

If they are equal then its a Yes or else No

Now compare value becomes Yes if there is at least one Yes for anchor and target dealer combination

else it becomes a No. 

I have not added Fvalue to avoid confusion

Bottom line is for each anchor and target dealer combination there will be either a  Yes or No

[JarroVGIT · Accepted solution]

Alright interesting case! I came up with the following solution.

Step 1: Dubplicate the anchor column and split the duplicate column by delimiter, in this case by semi-column. I created a practice table with more than 2 anchors per row, so I got more columns than 2. I don't know how many anchors you can have in that column per row, but I didn't think it would be a maximum of 2.

My original test tableDuplicated the Anchor columnSplitting by delimiter created 4 additional columns

Step two is to create a custom column that checks if the Target column contains any of the values. THis is the formula I used:

if Text.Contains([Targets], Text.From([#"Anchors - Copy.1"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.2"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.3"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.4"])) then "yes" else "no"

This results in another column with "yes" or "error". The errors are because for some reason I couldn't compare a text value (Targets) with null values, see my third screenshot. You can replace errors by 'no' by right clicking on the new column and click 'Replace errors' and replace it with 'no'./ Then remove all columns you don't need anymore (the duplicate, all delimited columns) and you are left with this:

Here my total M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSrROsk5W0lFKsk5RitUB8cuAvFTrNCgvyTrNOh0okmidbp0KEwOrT0bipYDkYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Anchors"}, {"Column2", "Targets"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Anchors", "Anchors - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Anchors - Copy", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Anchors - Copy.1", "Anchors - Copy.2", "Anchors - Copy.3", "Anchors - Copy.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Targets", type text}, {"Anchors - Copy.1", type text}, {"Anchors - Copy.2", type text}, {"Anchors - Copy.3", type text}, {"Anchors - Copy.4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([Targets], Text.From([#"Anchors - Copy.1"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.2"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.3"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.4"])) then "yes" else "no"),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", "no"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Errors",{"Anchors - Copy.1", "Anchors - Copy.2", "Anchors - Copy.3", "Anchors - Copy.4"})
in
    #"Removed Columns"

Hope this helps! That was fun to do 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂

[Anonymous]

Wow, Thanks for spending so much time on this.

I'll try this. I thought it would be an easier thing to do. Thanks a lot

[JarroVGIT]

No problem, this forum is where people help each other 🙂 Just don't forget to like and mark as the solution!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂