Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Tomaszsz
Regular Visitor

Ranking - problem when filtering only one value

Hi All,

 

I have a problem with RANKX function. I need to prepare a report in which a user will be able to select one category value, and will see rank in terms of a measure of that value.

 

I've prepared a DAX function, but it doesn't work in each case, sometimes the rank of selected category is different than the actual (usually by 1 place).

 

My data table looks like this (40k rows):

Tomaszsz_1-1671622360446.png

 

I want to rank values from the 'Category A' column by the sum of values in the "Measure 1' column

 

Dax function: 

Rank Category A by Measure 1 = 
IF(
    HASONEVALUE(data[Category A]),
    RANKX(
        ALL(data[Category A]),
        calculate(SUM(data[ Measure 1 ]))
    )
)
 

It works fine for most cases, but sometimes the rank number is wrong, as in the below screenshot. If we look at the bigger table with all categories then selected '6_Cat' has rank number 6 (and it's correct), but when we filter out only 6_Cat (table on the right) then its place is 5 (what is wrong).

Tomaszsz_2-1671622974126.png

I've noticed that when there is less data then the wrong rank appears less often, but still, it is.

 

Would be very grateful for the help, thanks in advance!

7 REPLIES 7
amontoulieu
Frequent Visitor

Hello @Tomaszsz , I have the same issue, et no way to find out why?
Any progress on this problem?

Thx

Hi @amontoulieu , unfortunately, I still do not have solution

Hi @Tomaszsz , try to use HASONEFILTER, it worked for me.

Hey @amontoulieu , thanks for the info but still not working.

It's fine when there is no filter on dates, but when I change the data range, then the result is different. On the screen below Category 10 should have rank 8, but it shows 3.

 

Tomaszsz_0-1694592480257.png

 

 

 

v-yiruan-msft
Community Support
Community Support

Hi @Tomaszsz ,

I created a sample pbix file(see the attachment), please check if that is what you want.

yingyinr_0-1671703036758.png

If the above one can't help you get the desired result, please provide some sample data in your table 'data'  (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

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

Hello @v-yiruan-msft ,did you find a moment to look into this? any idea what might be wrong?

Please find below the screen on which I've marked the wrong rank

 

Tomaszsz_0-1674474930852.png

 

Hey @v-yiruan-msft , thank you for your help.

 

Unfortunately, your solution is the same as mine, and there are rare cases in which filtered rank shows bad values. For example 27_Cat and 6_Cat are wrong (in my file)

 

Please find a link to my pbix file:

https://www.dropbox.com/s/fhbpj7pgragr2g6/Ranking%20test.pbix?dl=0

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.