Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
17 | |
17 |