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

      • damit23183's avatar
        damit23183
        Microsoft Employee
        6 years ago

        Hi,

         

        Still same result.

         

        Looks like BLANK is the issue which must remove.

         

        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.