Top ranked value in column and second most common value

[Anonymous]

Hi guys, 

I am trying to create two separate measures:

Measure 1 - Returns the most common value in column1

Measure 2 - Returns the second most common value in column1

Here's some context: I am using Power bi report builder and I already have a measure that ranks most common and second most common. But when I transfer it from power bi to report builder it shows the same value twice. It works when my measure is only ranking one value for some reason. 

So if I can create two separate measures, it should work. But I have spent hours trying to make it work and I can't. 

[v-yueyunzh-msft]

Hi , @Anonymous 

According to your description, you want to get the top2 common value in column1?

If this , you can refer to :
(1)This is my test data:

(2)We can create two measures like this:

Measure1 = var _t =ADDCOLUMNS( ALLSELECTED('Table'[Column1]) , "count" , CALCULATE( COUNT('Table'[Column1])))
var _t2 =  ADDCOLUMNS(_t , "rank" ,  RANKX(_t, [count],,DESC,Dense))

return
CONCATENATEX(SELECTCOLUMNS(FILTER(_t2,[rank]=1),"Column1",[Column1]),[Column1],",")

Measure2 = var _t =ADDCOLUMNS( ALLSELECTED('Table'[Column1]) , "count" , CALCULATE( COUNT('Table'[Column1])))
var _t2 =  ADDCOLUMNS(_t , "rank" ,  RANKX(_t, [count],,DESC,Dense))

return
CONCATENATEX(SELECTCOLUMNS(FILTER(_t2,[rank]=2),"Column1",[Column1]),[Column1],",")

Then we can get the result as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Anonymous]

@v-yueyunzh-msft

Thanks for this. It does work in the way you mentioned however, I think I didn't explain this properly. I have a report in power bi report builder and I need to show the two most common values. When I add the measures to my dataset in Power BI Report Builder and use the following expression =MAX(Fields!First_Most_Ranked.Value, "Dataset5") it shows a random result that is not correct. The measures work in Power BI desktop but when I bring them over to power bi report builder, it doesn't seem to work. 

[v-yueyunzh-msft]

Hi , @Anonymous 

Thank you very much for your reply, you are using report builder. Sorry I have no experience with report builder. For your question, you can try to submit your requirement to the Report Server forum, the Report Server forum supports some related issues of Report Builder.

Report Server - Microsoft Fabric Community

Best Regards,

Aniya Zhang

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