Comma separated value for same rank in table

[pbiforum123]

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?

[Samarth_18]

Hi @pbiforum123 ,

You can write a measure as below:-

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

Thanks,

Samarth

[pbiforum123]

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

[Jihwan_Kim]

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]

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

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

[pbiforum123]

@Jihwan_Kim  Thanks a lot for this. I will check and let you know..

Since we are creating the the table in the Power BI itself will it work if we apply filter in the Slicer?

[Jihwan_Kim]

Hi,

Thank you for your feedback.

I don't think it will work if the slicer is created in the visualization tab. In this case, I think table visualization has to be created, and this approach will be more suitable. But, because I do not know what types of slicer you are going to create, please try.

Thanks.

[pbiforum123]

@Jihwan_Kim  Thanks a lot for your help! Yes we need this table in the visual not in the query editor.  In the visual they may have slicer like quarter, year, brand, etc...

Sorry if I did not explain the requirement cleary at first.

Any idea how this can be achieve in the visuals using the DAX?

Is it even achievable?

[Jihwan_Kim · Accepted solution]

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

[pbiforum123]

@Jihwan_Kim Thank you so much, it is working perfectly. Could you please do me one favour? Whenever you are free could you please add the comments with your code explain how it works. Though it is working I am getting how it is working. So would like to understand that.

[Jihwan_Kim]

Hi,

I tried to write a comment in the measure like below.

Please check the below picture and the attached pbix file.

[pbiforum123]

@Jihwan_Kim Thanks a lot for this. Sure I will take a look tomorrow as I am little busy today.