cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## RANKXX problem

I'm working on a result report on sport activities. Data source is a SQL server database.

The report shall show results in a table based on field parameter slicer
Ranking
- Daily
- Weekly
-Monthly
- Yearly

The overall ranking result is workung well, as I calculate the ranking in sql server queries and those shall be always visible independent on any filter set

Problem
I created a measure 'mRankOnFilterActyVolts', which shall create a sub ranking based on filter
- Gender
- Nation
- ....

My problem is, that I only want to see the best result from the users based on TotalCalculatedPower.

Actually I stuck on getting the max value in the context of a week, month, year

Measure

mRankOnFilterActyVolts =
IF(
HASONEVALUE(RfvRefined[TotalCalculatedPower]),
//var maxVolts = MAX(RfvRefined[TotalCalculatedPower])

var LookupTable =
ALLSELECTED(RfvRefined),
"@sumVolts"RfvRefined[TotalCalculatedPower]
)

var currentValue = SELECTEDVALUE(RfvRefined[TotalCalculatedPower])
var Result =
RANKX (LookupTable[@sumVolts] , currentValue)
RETURN
Result
)

I suspect the 'ALLSELECTED'  but have actually no idea how to solve it, but didn't find a solution

1 ACCEPTED SOLUTION
Frequent Visitor

Hello Icy

my formula didn't work on further subfilter like , Gender and Country/Nation. The correct solution is

_max =
CALCULATE (
MAX ( RfvRefined[TotalCalculatedPower] ),
ALLSELECTED ( RfvRefined ),
)
_mRank =
RANKX ( ALLSELECTED ( RfvRefined ), [_max],, DESC,Dense)

I thank you for contribution. I still work on further ranking results, like
Average by 6 best results in a month for each user

Best Chris
4 REPLIES 4
Community Support

Hi @TooOld ,

Based on my test, your measure works. Do you mean you want to modify the expression? If so, try this:

``````mRankOnFilterActyVolts 2 =
IF (
HASONEVALUE ( RfvRefined[TotalCalculatedPower] ),
RANKX (
ALLSELECTED ( RfvRefined ),
CALCULATE ( MAX ( RfvRefined[TotalCalculatedPower] ) )
)
)
``````

In addition, if you just want to show the top1 value in the table visual, you can set filter like so:

Best Regards,

Icey

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

Frequent Visitor

Hello Icy

my formula didn't work on further subfilter like , Gender and Country/Nation. The correct solution is

_max =
CALCULATE (
MAX ( RfvRefined[TotalCalculatedPower] ),
ALLSELECTED ( RfvRefined ),
)
_mRank =
RANKX ( ALLSELECTED ( RfvRefined ), [_max],, DESC,Dense)

I thank you for contribution. I still work on further ranking results, like
Average by 6 best results in a month for each user

Best Chris
Resolver I

Hi TooOld,

Could you show the output you want?

Best regards.

Frequent Visitor

At time I got the result

 Weekly Rank UserName Gender Age Nation ActyVolts Subfilter 1 Jerms M 36 IT 7896 1 2 Ajas F 33 CH 6706 2 3 Arlind F 27 CH 5697 3 4 Sepp M 57 CH 3200 6 5 Soldo F 40 DE 245 9

The Column Weekly Rank represents the overall ranking of the username based on column ActyVolts. Weekly Rank = Overall Ranking (Done in SQL Server)

The Column Subfilter shall show different rankings based on slicer and not change the Weekly Ranking
- Nation
- Gender

No Gender or Nation filter from slicer set - expected

 Weekly Rank UserName Gender Age Nation ActyVolts Subfilter 1 Jerms M 36 IT 7896 1 2 Ajas F 33 CH 6706 2 3 Arlind F 27 CH 5697 3 4 Sepp M 57 CH 3200 4 5 Soldo F 40 DE 245 5

Gender set to Women Only (Slicer Gender set to F))

 Weekly Rank UserName Gender Age Nation ActyVolts Subfilter Nation = F (Female) 2 Ajas F 33 CH 6706 1 3 Arlind F 27 CH 5697 2 5 Soldo F 40 DE 245 3

Nation set to 'CH' (Slicer Nation)

 Weekly Rank UserName Gender Age Nation ActyVolts Subfilter Nation = CH (Slicer) 2 Ajas F 33 CH 6706 1 3 Arlind F 27 CH 5697 2 4 Sepp M 57 CH 3200 3

To sum up
In Sports there is an overall ranking for a contest, but participants expects to see a filtered ranking
- Women
- Nation
- AgeGroup

I thank you for helping here out

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors