Create a custom column or measurement to sort then hide duplicates

[dakins]

I am looking for help to create a custom_column by product_sort_order then hide duplicate person_ID in a report. My current solution is to create the report, add the product_sort_order column, then sort from highest to lowest, export to excel then remove duplicate by person_id

Can I automate this hierarchy selection process?

Person_ID	Person_name	Product_name	Product_sort_order
45039	Max	Table	3
45001	Sharma	Chair	2
45039	Max	Bench	1
45001	Sharma	Bench	1
45002	Kathy	Table	3
45039	Max	Chair	2
45001	Sharma	Chair	2

Desired solution

All duplicate person_id will be removed based on sort_order

Person_ID	Person_name	Product_name	Product_sort_order
45039	Max	Table	3
45002	Kathy	Table	3
45001	Sharma	Chair	2

[mahoneypat]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE1MLZU0lHyTawAkiGJSTmpQNpYKVYHLGdgCOQFZyQW5SYCGc4ZiZlFQNoIJo2k1Sk1LzkDSBti14opbQTkeSeWZFRisxfJYAxLcbopFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person_ID = _t, Person_name = _t, Product_name = _t, Product_sort_order = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Person_ID", "Person_name"}, {{"allrows", each _, type table [Person_ID=nullable text, Person_name=nullable text, Product_name=nullable text, Product_sort_order=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {"allrows", each Table.Max(_, "Product_sort_order")}),
    #"Expanded allrows" = Table.ExpandRecordColumn(Custom1, "allrows", {"Product_name", "Product_sort_order"}, {"Product_name", "Product_sort_order"})
in
    #"Expanded allrows"

Pat

[dakins]

Hi Pat,

Thanks for the solution it worked on the sample above however, I only made up that sample to explain what I needed help with. My PB instance is connected to a database with over 20 tables with different cardinalities. Is it possible to write a dax for the solution above?

Many thanks,

Dan

[v-rzhou-msft]

Hi @dakins 

Please try this dax code.

Dax Table = 
VAR _Sample = ADDCOLUMNS('Sample',"Rank",RANKX(FILTER('Sample','Sample'[Person_ID] = EARLIER('Sample'[Person_ID])),'Sample'[Product_sort_order],,DESC))
RETURN
SUMMARIZE(FILTER(_Sample,[Rank] = 1),[Person_ID],[Person_name],[Product_name],[Product_sort_order])

Result is as below.

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. 

[dakins]

Hello Rico,

Thanks for the soultion. When I tried it, I got the error below "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"


Please note, person and products are different tables while Product_sort_order is a calculated column.