Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
6 years ago
Solved

Multirow into Single Row for Multiple Column

Hi,   I am trying to convert multirow into single row using DAX formula, but not getting expected result becuase there is comma i am getting everywhere at begining of value. Please see below for mo...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  damit23183 

    Just add a filter to remove blank as below:

    SWITCH(TRUE(), NOT(ISBLANK([Basis])),

    CALCULATE(
    CONCATENATEX ('Table', [Basis],", ", [caseID]) ,
    FILTER ( 'Table', 'Table'[CaseID] = EARLIER ( 'Table'[CaseID] )&&'Table'[Basis]<>BLANK()
    )), BLANK()
    )

     

     

    By the way, I would suggest you use this formula to create a new table

    New Table = SUMMARIZE(
        'Claims',
        [CaseID],
        "Title",CONCATENATEX(DISTINCT('Claims'[Title]),[Title],","),
        "Type",CONCATENATEX(DISTINCT('Claims'[Type]),[Type],","),
        "Basis",CALCULATE(CONCATENATEX(DISTINCT('Claims'[Basis]),[Basis],","),FILTER('Claims','Claims'[Basis]<>BLANK()))
    )

    or

    New Table = SUMMARIZE(
        'Claims',
        [CaseID],
        "Title",CALCULATE(CONCATENATEX(DISTINCT('Claims'[Title]),[Title],","),FILTER('Claims','Claims'[Basis]<>BLANK())),
        "Type",CALCULATE(CONCATENATEX(DISTINCT('Claims'[Type]),[Type],","),FILTER('Claims','Claims'[Basis]<>BLANK())),
        "Basis",CALCULATE(CONCATENATEX(DISTINCT('Claims'[Basis]),[Basis],","),FILTER('Claims','Claims'[Basis]<>BLANK()))
    )

     and here is sample pbix file, please try it.

     

     

    Regards,

    Lin

DataZoe's avatar
DataZoe
Microsoft Employee
6 years ago

damit23183 I should probably make some fake data at this point, but one last try:

 

CALCULATE (
        CONCATENATEX (
            FILTER ( VALUES ( Claims[Basis] ), ISBLANK ( Claims[Basis] ) = FALSE () ),
            Claims[Basis],
            ", "
        ),
        FILTER ( Claims, Claims[CaseID] = MIN ( Claims[CaseID] ) && ISBLANK ( Claims[Basis] ) = FALSE () )
    )

damit23183's avatar
damit23183
Microsoft Employee
6 years ago

Hi,

 

Thanks for another suggestion.

 

Last sugggestion also gave me same result with comma ahead of the value. However, thanks a lot for all suggestion made by you as I got to learn something new too.

 

Thanks