Forum Discussion

pbiforum123's avatar
pbiforum123
Post Patron
3 years ago
Solved

Comma separated value for same rank in table

We have created the table with Rank like below.

 

 

What Client is expecting is below. Instead of showing multiple rows for the same rank they want show only one row for the companies with same rank.

 

 

Please note that Rank here is a measure(Rank with Dense).

Can someone help on this?

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Thank you for your feedback, and please check the below picture and the attached pbix file whether it suits your requirement.

     

     

     

12 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

     

    New Table =
ADDCOLUMNS (
    DISTINCT ( Data[Rank] ),
    "@CompanyList", CALCULATE ( CONCATENATEX ( Data, Data[Company], ", " ) )
)

     

    • pbiforum123's avatar
      pbiforum123
      Post Patron
      3 years ago

      Jihwan_Kim We dont have Rank as a column. In our case it is a measure. In your case you are having it as a column?

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        I tried to create a sample pbix file like below.

        Please check whether it suits your requirement, or please share your sample pbix file's link with the desired result, and then I can try to look into it.

        The below is for creating a new table.

         

         

         

        New Table = 
VAR _ranktable =
    ADDCOLUMNS ( Data, "@Rank", RANKX ( Data, Data[Value],, DESC, DENSE ) )
VAR _newtable =
    ADDCOLUMNS (
        SUMMARIZE ( _ranktable, [@Rank] ),
        "@CompanyList",
            CONCATENATEX (
                FILTER ( _ranktable, [@Rank] = EARLIER ( [@Rank] ) ),
                Data[Company],
                ", "
            )
    )
RETURN
    _newtable
  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    Hi pbiforum123 ,

     

    You can write a measure as below:-

    Measure_ = 
CONCATENATEX ( VALUES ( 'Table (2)'[Company] ), [Company], "," )


    Thanks,

    Samarth

     

    • pbiforum123's avatar
      pbiforum123
      Post Patron
      3 years ago

      Samarth_18 I tried this but it is not working can you please share the sample pbix file.

       

      Please note that in our case Rank that you see here is a measure.