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 maybe try this one:

 

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

damit23183's avatar
damit23183
Microsoft Employee
6 years ago

Hi,

 

Still same result.

 

Looks like BLANK is the issue which must remove.

 

Thanks

  • 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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    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

    • damit23183's avatar
      damit23183
      Microsoft Employee
      6 years ago

      Hi,

       

      Thanks for your response.

       

      Your solution is working perfectly by just adding && condition in SWITCH formula which did not even think of it.

       

      Thank you so much. 

       

      I can't create new table though because there other information needed from same table so it can become complext. Therefore, I was trying to get it work in same table.

       

      Thanks again for your help.