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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
LukasV89
Frequent Visitor

RANKX does not work for me. Any help appreciated

Capture.PNG

Helo everybody,

 

I have been fighting with the RANKX function. My data looks like this:

 

Name                Member_ID              Tickets Closed       Tickets Worked     Survey Score           Date

 

John B               123456                              35                    20                              88%                  1/1/2017

John B               123456                              12                    10                              98%                  1/2/2017

ALice N              234567                              22                    15                              95%                  1/1/2017

ALice N              234567                              14                    23                              75%                  1/2/2017

 

What I am trying to achieve is to create four columns:

 

1) Ranking by CLosed TIckets

2) Ranking by TIckets Worked (Touches)

3) Ranking by Survey Score

4) Total Ranking (Smalles product of all rankings = Number 1)

 

I also have a date slicer so I can look at the these metrics by specific days. I have had a similar data set where I had for each record 1 row and it worked. For this report I am uploading an aggregate data set by Name, and date. I am not sure if this really has anything to do with this.

 

At this point, I have tired many different formulas and the main issue I was running into was that I got 1's for every single person meaning I ranking each person seperately. This was my original formula:

 

Name of my table is TEST:

 

ranking 6 = RANKX(ALLSELECTED(TEST[Member_ID]),CALCULATE(SUM(TEST[Closed Tickets])))

 

Then I did some research and I have found this:

 

MM Closed tickets = Sum(TEST[Closed Tickets])

Tickets Ranking = if(HASONEVALUE(TEST[Member_ID]),RANKX(ALLSELECTED('TEST'),[MM Closed Tickets]),BLANK())

 

I get a ranking order now but it is partially wrong. Also, when I select specific days, it is working correctly. When I select all days, the rankings are off. Please see the attached screenshot on the top

 

I would really appreciate any advice or help on this one. Thank you.

 

Lukas

 

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

Hi @LukasV89,

 

You could use the DAX expression below to create rank column.
rankbyclosed = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Tickets Closed],,)
rankbyWorked = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Tickets Worked],,)

rankbySurvey = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Survey Score ],,)
Capture.PNGUntitled1.png

Regards,

Charlie Liao

View solution in original post

1 REPLY 1
v-caliao-msft
Employee
Employee

Hi @LukasV89,

 

You could use the DAX expression below to create rank column.
rankbyclosed = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Tickets Closed],,)
rankbyWorked = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Tickets Worked],,)

rankbySurvey = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Survey Score ],,)
Capture.PNGUntitled1.png

Regards,

Charlie Liao

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.