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

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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

I did this:

Table 2 = 
    SUMMARIZE(
        'Table',
        [CaseID],
        "Title",CONCATENATEX(DISTINCT('Table'[Title]),[Title],","),
        "Type",CONCATENATEX(DISTINCT('Table'[Type]),[Type],","),
        "Basis",CONCATENATEX(DISTINCT('Table'[Basis]),[Basis],",")
    )

See attached.