Multirow into Single Row for Multiple Column

[damit23183]

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

[DataZoe]

@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]

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]

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.

[DataZoe]

@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]

Hi,

Still same result.

Looks like BLANK is the issue which must remove.

Thanks

[v-lili6-msft · Accepted solution]

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]

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.

[DataZoe]

@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]

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

[AllisonKennedy]

@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