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
March Fabric Community Update

Fabric Community Update - March 2024

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

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.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.