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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hhaimer
Frequent Visitor

Ranking people by week - DAX

Hello everyone,

I tried to create a ranking measure to rank people who has a highest number of resolved task by week using DAX but it doesn't work

_rankResolvedTasks =
IF(
    ISINSCOPE( Tasks[Responsable] ),
    RANKX(
        ALLSELECTED(Tasks[Responsable]),
        [_countTasksResolved]
    )
)

 

The data table is in this form:

weekassigneetaskstatus
week 1developer Atask 1resolved
week 1developer Atask 2resolved
week 1developer Btask 3resolved
week 2developer Btask 4resolved
week 2developer Btask 5resolved
week 2developer Btask 6resolved


I want to get a measure that count :

weekdeveloperResolved TaskRank
week 1A2

1

 B12
week 2A02
 B3

1

 

Thank you in advance for your help !!

3 ACCEPTED SOLUTIONS
ppm1
Solution Sage
Solution Sage

Please try a measure like this instead, using your actual table/column/measure names. Note that since the Assignee column is in the same table and A has no rows, they do not show in Week 2. If you make a separate Assignee table and relate it, you can get it show with a +0 type measure (not best practice to do +0).

ResolvedRank =
VAR vAssigneesInScope =
    CALCULATETABLE (
        DISTINCT ( Tasks[assignee] ),
        REMOVEFILTERS ( Tasks[assignee] )
    )
RETURN
    RANKX ( vAssigneesInScope, [ResolvedTasks],, DESC )

ppm1_0-1669813316392.png

Pat

 

 

Microsoft Employee

View solution in original post

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Jihwan_Kim_1-1669813486456.png

 

 

Jihwan_Kim_0-1669813471045.png

 

Rank resolved count by week: =
IF (
    HASONEVALUE ( Week[week] ),
    RANKX ( ALL ( assign[assignee] ), [Resolved count:],, DESC )
)

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1669819775118.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1669819775118.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Jihwan_Kim_1-1669813486456.png

 

 

Jihwan_Kim_0-1669813471045.png

 

Rank resolved count by week: =
IF (
    HASONEVALUE ( Week[week] ),
    RANKX ( ALL ( assign[assignee] ), [Resolved count:],, DESC )
)

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


ppm1
Solution Sage
Solution Sage

Please try a measure like this instead, using your actual table/column/measure names. Note that since the Assignee column is in the same table and A has no rows, they do not show in Week 2. If you make a separate Assignee table and relate it, you can get it show with a +0 type measure (not best practice to do +0).

ResolvedRank =
VAR vAssigneesInScope =
    CALCULATETABLE (
        DISTINCT ( Tasks[assignee] ),
        REMOVEFILTERS ( Tasks[assignee] )
    )
RETURN
    RANKX ( vAssigneesInScope, [ResolvedTasks],, DESC )

ppm1_0-1669813316392.png

Pat

 

 

Microsoft Employee
philouduv
Resolver III
Resolver III

Hello @hhaimer 

If I understood correctly you want to rank each developper per week according to their number of resolved task.
I changed week X by X so it's cleaner.
First I created a column to calculate the number 

philouduv_0-1669812867189.png


Then, I created the rank for each week from this number:

Rank = RANKX(Filter(Table1,
Table1[week] = EARLIER(Table1[week]))
,Table1[nbr_resolved_task_per_week],,,Dense)

Adding Dense to not take in consideration the same developper twice
Which gives that :
philouduv_2-1669813031997.png

You need to specify not summarize for the column when you put it in your table.

Hope it helps 
Have a good day



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors