DAX code or Grouping solution for multiple columns

[TTPP81]

Please provide DAX code/conditional formatting solution to achieve table 2 below. 

Table one (below) - Each model name either has a model family or no model family. And each model name in a model family may have their own individual or similar valuation date.

Table 2 (below): I am trying to achieve a result that looks like Table two. If a model names has a model family in Table 1 and has a different valuation date from other model names in the family, then the Model name/Family column in table 2 will pick the individual Model Name instead of its Family and reflects each individual valuation date. e.g. Mining Road, Mining bridge, Mining Detail and Rostered Source.

If a model name has a model family in Table 1 and have the same valuation date with other model name, then the Model name/Family column in table 2 will pick the Family name instead as they all have the same valuation date. E.g. Cliq Group.

@BA_Pete @shafiz_p 

[BA_Pete]

Hi @TTPP81 ,

Glad you found a solution that worked for you here.

However, I hope you can find some time to let me know why you chose a solution with two merges and a group by in it rather than my two group by solution.

Did the option you chose give you better performance, or did you find it simpler to follow, or another reason?

I'm just curious as to how I can improve my solutions in future.

Thanks,

Pete

[TTPP81]

hi BA_Pete, thank you for the msg and input. I didnt test out your solution as my above question was just sample template. If i used your codes in my actual dashboard, i might need to spend some time tidying up the existing codes and renaming conventions so just to play it safe, i used the first option (with the assistance of visuals). I have kept your codes in mind in the future, in case the first solultion becomes obsolete for my current dashboard. Thank you again. Appreciate your help. 

[shafiz_p · Accepted solution]

Hi @TTPP81  Since Table2[Model Name/Family] have distinct value. You could acheive your desired result by merging with Table1 twice and group technique. See solution images (In My case it is Table3 and Table4):
First merge:

Second Merge:

Now expand both the column (Only valuation date column):

After expanding both the column you will get this table. 

Now merge both column without seperator.

Now use group by transformation and use Max aggregation:

After that you will get your desired result:

Here is the complete M code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Model Name / Family", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Model Name / Family"}, Table3, {"Model Name"}, "Table3", JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"Model Name / Family"}, Table3, {"Family"}, "Table3.1", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries1", "Table3", {"Valuation Date"}, {"Valuation Date"}),
    #"Expanded Table3.1" = Table.ExpandTableColumn(#"Expanded Table3", "Table3.1", {"Valuation Date"}, {"Valuation Date.1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Table3.1", {{"Valuation Date", type text}, {"Valuation Date.1", type text}}, "en-US"),{"Valuation Date", "Valuation Date.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Valuation Date.2"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Model Name / Family"}, {{"Valuation Date", each List.Max([Valuation Date.2]), type text}})
in
    #"Grouped Rows"

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[BA_Pete]

Hi @TTPP81 ,

We can do this in PQ by using two Group Bys - one to count the number of distinct dates in each [Family] group, then another to regroup on a curated [Model Name / Family] list.

Paste this into Advanced Editor to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJda8IwFIb/Sui1kCa6OS9dR4cXbuDEfYgXoTnoYTGpWbrhv19kW7OUaTshHMjL85CTw1kuk8yCREdysUW1J1MjQSW9n5TVQcqoPzzlPFn1Wix+ltU/Zt3RsQ9zC7sKdLEn2QaK1wgeNOFHge9gT5NT1KjXzWa+05kRss54n6bDjuK1RbmGoA66qzfgBKqgjmh61abOzJsDPz/yYCpbQPTXUXMq422pgEy0hBJ80S7CLwK+EAqlcGg0ubWmKmtubgHIvQ7vMEYZ7yrOP8yZ4uZQfzd7eUJ9en6J9dzPJtj8v7ZfpWN2pnD35R322F8qP99J1OmwhV78Sa8+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Family = _t, #"Model Name" = _t, #"Valuation Date" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Valuation Date", type date}}),

// Relevant steps from here ===>
    repNA_ModelName = Table.ReplaceValue(chgTypes,"N/A", each [Model Name], Replacer.ReplaceText,{"Family"}),
    groupRows_Counts = Table.Group(repNA_ModelName, {"Family"}, {{"CountDates", each List.Count(List.Distinct([Valuation Date])), type number}, {"data", each _, type table [Family=nullable text, Model Name=nullable text, Valuation Date=nullable date]}}),
    expandData = Table.ExpandTableColumn(groupRows_Counts, "data", {"Model Name", "Valuation Date"}, {"Model Name", "Valuation Date"}),
    addModelNameFamily = Table.AddColumn(expandData, "Model Name / Family", each if [CountDates] = 1 then [Family] else [Model Name]),
    groupRows = Table.Group(addModelNameFamily, {"Model Name / Family", "Valuation Date"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    groupRows

Summary:

repNA_ModelName = Swap N/A values in [Family] for [Model Name].

groupRows_Counts = Group table on Family, getting a distinct count of dates within that family and adding a nested table.

expandData = Expand our nested table back out, restoring our original table data.

addModelFamily = If there's more than one distinct date value in the [Family] group, then use [Model Name], otherwise use [Family].

groupRows = Regroup the table using the new curated [Model Name / Family] column instead.

Output:

Pete