Rankx problems ( Rankx within group, rankx and dateadd, rankx with slicer)

[Anonymous]

Hello everyone,

I found Rankx can be completed if used as a measure. Any help will be greatly appreciated! 

I have built a Date table and one to many relationship with the Test Table on the “Date” Column ( Please see the screenshot below).

I would like to achieve the following:

Date	Link Code	Volumes	PM Volumes	Rank	PM Rank
01/04/2021	A	18		3	1
01/04/2021	B	62		1	1
01/04/2021	C	19		2	1
01/05/2021	A	12	19	3	2
01/05/2021	B	103	12	1	3
01/05/2021	C	15	103	2	1
01/06/2021	A	33	15	1	3
01/06/2021	B	18	33	3	1
01/06/2021	C	29	18	2	2

However, this is what returned with my Dax below

Date	Link Code	Volumes	PM Volumes	Rank	PM Rank
01/04/2021	A	18		3	1
01/04/2021	B	62		1	1
01/04/2021	C	19		2	1
01/05/2021	A	12	19	3	1
01/05/2021	B	103	12	1	1
01/05/2021	C	15	103	2	1
01/06/2021	A	33	15	1	1
01/06/2021	B	18	33	3	1
01/06/2021	C	29	18	2	1

Rank =

RANKX(filter(all('Test table'[Date],'Test table'[Link Code]),'Test table'[Date]=max('Test table'[Date])),CALCULATE(sum('Test table'[ Volumes])),,DESC)

PM Volumnes = CALCULATE(sum('Test table'[ Volumes]),dateadd('Dim Date 2'[Date],-1,month))

PM rank =

rankx(filter(all('Test table'[Date],'Test table'[Link Code]),'Test table'[Date]=max('Test table'[Date])),[PM Volumnes],,desc)

May I ask where I did wrong? Why PM Rank doesn’t return as expected?

Thank you.

Jing

[amitchandak]

@Anonymous with time intelligence functions you need to use date table in visual too

try with these changes and date table in visual too

rankx(filter(all('Test table'[Date],'Test table'[Link Code]),'Test table'[Date]=max('Test table'[Date])),[PM Volumnes],,desc)

[Anonymous]

Hi Amichandak

Thank you for your help.

I am using the Date column from the Date table ('Dim Date 2'[Date]) in visual. PM Rank just doesn't return as expected.

The rankx Dax you provided is the same with mine?

Thank you.

Jing

[amitchandak]

@Anonymous , Please use in the rank too

[Anonymous]

@amitchandak 

Hi Amitchandak

Thank you for your reply.

I thought about using the Date column from the Dim Date table ('Dim Date 2'[Date])before. Below is the Dax I tried.

rankx(filter(all('Dim Date 2'[Date],'Test table'[Link Code]),'Dim Date 2'[Date]=max('Dim Date 2'[Date])),[PM Volumnes],,desc)

However, becaues the All function can only be used if the columns are from the same table,  the fomular above returned error. 

I also tried rankx(filter(all('Dim Date 2'[Date],'Test table'[Link Code]),'Test table'[Date]=max('Test table'[Date])),,desc), but it still didn't work either.

Thank you.

Jing

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Please try this code:

pm rank =
RANKX (
    CALCULATETABLE (
        ALLSELECTED ( 'Table'[Link Code] ),
        ALLEXCEPT ( 'Dim Date', 'Dim Date'[Date].[Day] )
    ),
    [PM Volumes],
    ,
    DESC
)

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[Anonymous]

@v-chenwuz-msft 

Hello

Thank you so much for your help! It works well.

However, I don't really understand the code. Would you mind explaining the following code?

CALCULATETABLE (

ALLSELECTED ( 'Table'[Link Code] ),

ALLEXCEPT ( 'Dim Date', 'Dim Date'[Date].[Day] )

Thank you.

Jing