topic Re: Comparing column values and ignoring the order of element DAX in DAX Commands and Tips

Comparing column values and ignoring the order of element DAX

Sharma0815 — Mon, 30 Aug 2021 15:34:51 GMT

Hi All,

I have a column where I need to check whether the column value contains the same data in any order.
I want to get the first value as output value if the value matches in any order.

Can we do this using DAX ??

Example.

Combo code	Output
1059-0,1056-1	1059-0,1056-1
1056-1,1059-0	1059-0,1056-1
312-0,313-1,314-2	312-0,313-1,314-2
314-2,313-1,312-0	312-0,313-1,314-2

Re: Comparing column values and ignoring the order of element DAX

wdx223_Daniel — Tue, 31 Aug 2021 03:48:09 GMT

Output = VAR _tbl = ADDCOLUMNS ( ALL ( SampleTable[Combo Code] ), "@new", VAR _p = SUBSTITUTE ( 'SampleTable'[Combo Code], ",", "|" ) VAR _len = PATHLENGTH ( _p ) VAR _series = GENERATESERIES ( 1, _len ) VAR _items = ADDCOLUMNS ( _series, "@item", PATHITEM ( _p, [Value] ) ) VAR _items_with_idx = ADDCOLUMNS ( _items, "@idx", COUNTROWS ( FILTER ( _items, [@item] <= EARLIER ( [@item] ) ) ) ) RETURN CONCATENATEX ( _series, MAXX ( TOPN ( 1, FILTER ( _items_with_idx, [@idx] >= EARLIER ( [Value] ) ), [@idx], ASC ), [@item] ), "," ) ) RETURN MINX ( FILTER ( _tbl, 'SampleTable'[Combo Code] = SELECTEDVALUE ( SampleTable[Combo Code] ) ), [@new] )

Re: Comparing column values and ignoring the order of element DAX

CNENFRNL — Tue, 31 Aug 2021 18:50:58 GMT

Yes, DAX does the trick easily with some simple transformation of original dataset,

As to me, PQ solution is more straightforward and comprehensible.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwtdQ10AFSZrqGSrE6YBEgUwciAROBMY0NjYCqjQ2NgSqMDU10jTC0OELVAeXg6oyQdAP5RoYgfiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Combo code" = _t]), #"Added Custom" = Table.AddColumn(Source, "Codes", each Text.Combine(List.Sort(Text.Split([Combo code], ",")),",")), #"Grouped Rows" = Table.Group(#"Added Custom", {"Codes"}, {{"ar", each _, type table [Combo code=nullable text, Codes=text]}}), #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Combo code"}, {"Combo code"}) in #"Expanded ar"