topic Re: RANKXX problem in DAX Commands and Tips

RANKXX problem

TooOld — Sat, 18 Jun 2022 14:57:32 GMT

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

pbix File to download

Measure

mRankOnFilterActyVolts =

IF(

HASONEVALUE(RfvRefined[TotalCalculatedPower]),

//var maxVolts = MAX(RfvRefined[TotalCalculatedPower])

var LookupTable =

ADDCOLUMNS(

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

Re: RANKXX problem

ManguilibeKAO — Mon, 20 Jun 2022 06:19:35 GMT

Hi TooOld,

Could you show the output you want?

Best regards.

Re: RANKXX problem

TooOld — Mon, 20 Jun 2022 09:29:34 GMT

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

Re: RANKXX problem

Icey — Wed, 22 Jun 2022 09:51:53 GMT

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.

Re: RANKXX problem

TooOld — Sun, 26 Jun 2022 10:13:11 GMT

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 ),

VALUES ( RfvRefined[UserName])

)

_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