Forum Discussion

SteveDesmedt's avatar
SteveDesmedt
Frequent Visitor
2 years ago
Solved

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 ...
  • smpa01's avatar
    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] )