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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

rank with conditions

Hi,

 

I want to rank the Customer_ID based on the date column with the condition that if the date is longer than 61 days ago, the rank will start from 1 again as seen in below table:

Customer_ID    date  rank
aaa2020/2/1  1
aaa2020/2/1  1
aaa2020/2/3  2
aaa2020/3/1  3
aaa2020/7/1  1
aaa2020/7/1  1
aaa2020/7/2  2

 

I created two calculated columns, last date and the date between the dates:

 

Customer_ID    date  last date  time between
aaa2020/2/1      0
aaa2020/2/1      0
aaa2020/2/3   2020/2/1    2
aaa2020/3/1   2020/2/3    25
aaa2020/7/1   2020/3/1    122
aaa2020/7/1  2020/3/1    122
aaa2020/7/2   2020/7/1

    1

 

How can I calculate the rank?

 

I tried 

 

rank= 

IF(
[time between] = 0 || [time between] > 61, 1,
RANKX (
FILTER (
'table'
,'table'[Customer_ID] = EARLIER ( [Customer_ID] )
&& [shop_id] = EARLIER([shop_id])
&& [date] < EARLIER([date])
)
,'table'[date]
,
,ASC
,Dense
)
 
But this doen't give me the correct result. Please advise

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I was able to solve this issue by adding the following condition:
[date] >= EARLIER([date]) -61
 
frequency = RANKX (
FILTER (
'table'
,'table'[Customer_ID] = EARLIER ( [Customer_ID] )
&& [date] >= EARLIER([date]) -61
), [date],
,asc
, dense
)

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@Anonymous , All new columns

You can have few of them as var in a column if needed

 

last date= maxx(filter(Table,[customer] = earlier([customer])),[date])

Diff = datediff([date],[last date],day)
61 Flag = quotient([Diff],61)

Rank = RANKX (
FILTER (
'table'
,'table'[Customer_ID] = EARLIER ( [Customer_ID] )
&& [shop_id] = EARLIER([shop_id]) && [61 Flag] = EARLIER([61 Flag])
), [date],,asc, dense)

Anonymous
Not applicable

@amitchandak thanks for your reply.

your DAX function gives me the same result as the one I tried before.

condition: [time between] is greater than 61, then start rank from 1 and next date 2 and so on. 

In this example date: 2020/7/2 should be ranked as 2 not 4

キャプチャ.PNG

@Anonymous , Can remove the shop id and try. I just added as that was there in your script

 

Rank = RANKX (
FILTER (
'table'
,'table'[Customer_ID] = EARLIER ( [Customer_ID] )
&& [61 Flag] = EARLIER([61 Flag])
), [date],,asc, dense)

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

@amitchandak 

Hi, I think I figured where the issue lies.

 

I created a flag for 61 days,

-> quotient([time between]), 61)

but this flags only the dates where the [time between] is gretaer than 61 not the dates afterwards.

 

So when I used the below rank funtion, it ranks all the dates where there is no flag (including the dates after the 61 days flag), 

but I want that after 61 days the rank should start again from 1 and next date afterwards should be 2 and so on

 

Rank = RANKX (
FILTER (
'table'
,'table'[Customer_ID] = EARLIER ( [Customer_ID] )
&& [61 Flag] = EARLIER([61 Flag])
), [date],,asc, dense)

Anonymous
Not applicable

I was able to solve this issue by adding the following condition:
[date] >= EARLIER([date]) -61
 
frequency = RANKX (
FILTER (
'table'
,'table'[Customer_ID] = EARLIER ( [Customer_ID] )
&& [date] >= EARLIER([date]) -61
), [date],
,asc
, dense
)
Anonymous
Not applicable

@amitchandak  thanks,

Please find the pbix file in the below link:

https://1drv.ms/u/s!AsNOXcTG8UttgddMZkYv_tkF0luqVw

sreenub
Resolver II
Resolver II

Use the below RANKX

 

RANKX(
‘Table’,
‘Table'[time between]

,

ASC

)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.