Get top categories and concatenate as a text

[Anonymous]

I have a table like the following that includes a category column.

I'm looking for a measure to get the categories with the highest count and concatenate them together, while preserving all filters on the table.

Index	Category
1	A
2	B
3	A
4	B
5	A
6	A
7	C
8	D

The measure should return "A" as a text, since it has repeated the most.

Another example:

Index	Category
1	A
2	B
3	A
4	B
5	A
6	B
7	C
8	D

For this, the measure should return "A, B" as a text, since A and B are repeated the most.

Thanks for your help.

[CNENFRNL · Accepted solution]

Measure = 
VAR __sum = SUMMARIZECOLUMNS( INFO[Category], "@Cnt", COUNTROWS( INFO ) )
VAR __max = MAXX( __sum, [@Cnt] )
RETURN
    CONCATENATEX( FILTER( __sum, [@Cnt] = __max ), INFO[Category], ", " )

Another solution by Power Query

let
    Source = Table.Group(INFO, "Category", {"Count", Table.RowCount}),
    Cols = Table.ToColumns(Source),
    Pos = List.PositionOf(Cols{1}, List.Max(Cols{1}), Occurrence.All),
    Custom1 = Text.Combine(List.Accumulate(Pos, {}, (s,c) => s & {Cols{0}{c}}), ", ")
in
    Custom1

[CNENFRNL · Accepted solution]

Measure = 
VAR __sum = SUMMARIZECOLUMNS( INFO[Category], "@Cnt", COUNTROWS( INFO ) )
VAR __max = MAXX( __sum, [@Cnt] )
RETURN
    CONCATENATEX( FILTER( __sum, [@Cnt] = __max ), INFO[Category], ", " )

Another solution by Power Query

let
    Source = Table.Group(INFO, "Category", {"Count", Table.RowCount}),
    Cols = Table.ToColumns(Source),
    Pos = List.PositionOf(Cols{1}, List.Max(Cols{1}), Occurrence.All),
    Custom1 = Text.Combine(List.Accumulate(Pos, {}, (s,c) => s & {Cols{0}{c}}), ", ")
in
    Custom1

[Anonymous]

This is working very well on this example data. However, when I use it on my own large real data, I'm getting this error:

Couldn't load the data for this visual

MdxScript(Model) (1046, 13) Calculation error in measure 'Table'[measure]: SummarizeColumns() and AddMissingItems() may not be used in this context.

Do you know why I'm getting this error? Any alternative method? Maybe the error is due to blank values in the table? How to ignore them if blanks are the cause?

Thanks.

[Anonymous · Accepted solution]

Regarding the error I mentioned, I used a similar method explained in here to summarize the table. 

https://community.powerbi.com/t5/Desktop/Issue-with-Bar-chart-SummarizeColumns-and-AddMissingItems-m...

That solved my problem.

So, the final measure is:

Measure = 
// VAR __sum = SUMMARIZECOLUMNS( INFO[Category], "@Cnt", COUNTROWS( INFO ) )
VAR __sum = FILTER (
ADDCOLUMNS (
    SUMMARIZE (
        ALLSELECTED('INFO'),
        INFO[Category]
        ),
        "@Cnt", CALCULATE(COUNTROWS(INFO))),
        [@Cnt] > 0
        )
VAR __max = MAXX(__sum, [@Cnt])
RETURN
CONCATENATEX( FILTER( __sum, [@Cnt] = __max ), INFO[Category], ", " )