Forum Discussion
SteveDesmedt
2 years agoFrequent Visitor
Convert Rows to column and group in new column
Hi all, I'm struggling with following scenario : Table1 Entry No. Document Code Document Reference 1 B00 12345677 2 B00 AER445ER 3 C00 IUYYN/1225 1 C00 DEEFFD25 ...
- 2 years ago
SteveDesmedt A dax solution
Measure3 = VAR one = ADDCOLUMNS ( src, "test", VAR exp1 = CALCULATE ( MAX ( src[Document Code] ) ) & ":" & CALCULATE ( CONCATENATEX ( FILTER ( ALL ( src ), src[Entry No.] = MAX ( src[Entry No.] ) && src[Document Code] = MAX ( src[Document Code] ) ), src[Document Reference], " & ", src[Document Reference] ) ) RETURN exp1 ) VAR two = ADDCOLUMNS ( one, "test2", CONCATENATEX ( SUMMARIZE ( FILTER ( one, [Entry No.] = EARLIER ( [Entry No.] ) ), [test] ), [test], "||", [test], ASC ) ) RETURN MAXX ( two, [test2] )
smpa01
2 years agoCommunity Champion
SteveDesmedt A dax solution
Measure3 =
VAR one =
ADDCOLUMNS (
src,
"test",
VAR exp1 =
CALCULATE ( MAX ( src[Document Code] ) ) & ":"
& CALCULATE (
CONCATENATEX (
FILTER (
ALL ( src ),
src[Entry No.] = MAX ( src[Entry No.] )
&& src[Document Code] = MAX ( src[Document Code] )
),
src[Document Reference],
" & ",
src[Document Reference]
)
)
RETURN
exp1
)
VAR two =
ADDCOLUMNS (
one,
"test2",
CONCATENATEX (
SUMMARIZE ( FILTER ( one, [Entry No.] = EARLIER ( [Entry No.] ) ), [test] ),
[test],
"||",
[test], ASC
)
)
RETURN
MAXX ( two, [test2] )