How do I get the Top X value across columns in each row?

[RolandPlanet]

Hello, 

I'm trying to add a column where I get the first most occurring value across columns in a row along with the second most occurring value, and 3rd most occuring value

ID	Column1	Column2	Column3	Column4	Column5
1234	Apple	Apple	Orange	Apple	Orange
1235	Orange	Orange	Pear	Orange	Orange

In other words, I'd like a column that would show "Apple" as the first most occurring value and "Orange" as the second most occuring value in the first row.  How can I do that? 

[v-alq-msft]

Hi, @RolandPlanet 

You may go to Query Editor, go to 'Transform' ribbon, make 'ID' selected and click 'unpivot other columns'.  And then you need to click 'Close and Apply'.

You may create a measure as below.

Result = 
var _id = SELECTEDVALUE('Table'[ID])
var tab = 
SUMMARIZE(
    'Table',
    'Table'[ID],
    'Table'[Value],
    "Count",
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[Value] = EARLIER('Table'[Value])
        )
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Rank",
    RANKX(
        FILTER(
            tab,
            [ID] = _id
        ),
        [Count]
    )
)
return
CONCATENATEX(
    FILTER(
        newtab,
        [Rank] = 1
    ),
    [Value]
)&" "&
CONCATENATEX(
    FILTER(
        newtab,
        [Rank] = 2
    ),
    [Value]
)&" "&
CONCATENATEX(
    FILTER(
        newtab,
        [Rank] = 3
    ),
    [Value]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RolandPlanet]

This is great! However, if two values have the same number of occurences, they get concatenated together as "AppleOrange" for example. Is there a way to prevent this? 

[v-alq-msft · Accepted solution]

Hi, @RolandPlanet 

I have made a few changes on the measure. Please try the following measure to see if it works.

Result = 
var _id = SELECTEDVALUE('Table'[ID])
var tab = 
SUMMARIZE(
    'Table',
    'Table'[ID],
    'Table'[Value],
    "Count",
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[Value] = EARLIER('Table'[Value])
        )
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Rank",
    RANKX(
        FILTER(
            tab,
            [ID] = _id
        ),
        [Count]
    )
)
return
CONCATENATEX(
    FILTER(
        newtab,
        [Rank] = 1
    ),
    [Value],
    "-"
)&" "&
CONCATENATEX(
    FILTER(
        newtab,
        [Rank] = 2
    ),
    [Value],
    "-"
)&" "&
CONCATENATEX(
    FILTER(
        newtab,
        [Rank] = 3
    ),
    [Value],
    "-"
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@RolandPlanet 

Better you unpivot data

https://radacad.com/pivot-and-unpivot-with-power-bi

Then you can use all expect at ID level to get this answer

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390