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,

 

Still same result.

 

Looks like BLANK is the issue which must remove.

 

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.