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 more explanation;

 

This is source table,

 

 

 

 

 

So far i was able to get below result which is correct as expected for one of the dashboard;

 

 

 

However, i would like to keep above table, and in addition i would like to achieve the result as you can see below;

 

But, i am getting result like below;

 

So far, i got the value as expected but if you see red highlighted in screen shot above, there is comma "," before the value which is everywhere. Also, if there is no BASIS for any caseID, comma is still coming there.

 

This is formula i have used to get expected result;

 

Formula :  

 

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

CALCULATE(
CONCATENATEX (Claims, [Basis],", ", [caseID]) ,
FILTER ( 'Claims', 'Claims'[CaseID] = EARLIER ( 'Claims'[CaseID] )
)), BLANK()
)

 

I am using Filter on CASEID because if i dont use it then it gave the duplicates of BASIS in same row just like below; 

 

 

 

 

 

Any help would be appreciated!

 

Thanks

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

10 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago

    damit23183 I cannot replicate your problem. When I try with your sample data, I have no comma. 

     

    Does your full dataset contain some blanks for Basis? That might be what is causing the leading comma with no text before it. If so, you can use an IF or other option to check for blanks, or add a condition to the FILTER using && to check that Basis is not blank.

     

    Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

     

    If you found this post helpful, please give Kudos.

    I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

    https://sites.google.com/site/allisonkennedycv

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    6 years ago

    damit23183 you could try this:

     

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

     

    Edit: I should add, that this is a measure, and then you would put it and the CaseID in a table or matrix!

    • damit23183's avatar
      damit23183
      Microsoft Employee
      6 years ago

      HI,

       

      Thanks for your response,

       

      I tried your solution but same thing happend. 

       

      Comma came in for some cases. 

       

      It does look like some Blank cells available for specific Cases.

       

      If i want to add some filter or remove blank in existing formula you gave me then,

       

      How would i get rid of blank value for any cases in my scenario?

       

      Thanks

      • 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] ) )
            )