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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Datafruit
Helper I
Helper I

Measure to rank customers per category and date

Hello PBI community,

I would need help to get this measure working. What I'm in need is that I need to assign a rank for a customer per category and datetime value.
I had a calculated column RANKX working fine, see below:
calc column.png

 

But as users do not want a static ranking, rather a dynamic one, I'm in a pickle with this. The data format can be seen below:
PS! In the pic below I have export date and time separately, but I have a column for the export datetime (see snip above).
table.png
As one customer can be exported multiple times and is exported to partner A and partner B. Depending on the filters (Partner, ClientPIN, date, etc.) I would like to have the ranking. 
So for Jan2022 for this client and partner A rank 1 would be 31 Jan 2022 4:24:12 AM and partner B 31 Jan 2022 4:24:13 AM.

I really hope someone can help me out. Desperate here already!

1 ACCEPTED SOLUTION

Hi @amitchandak 
I tried your measure, but it expanded the selection to the whole table, ignoring the filters. 

I did though, get some help with the measure and ended up using 4 variables and a dummy tables:


Synt measure =
var _0 = NOT(SELECTEDVALUE('Remove duplicates'[Remove duplicates]) = "Yes")
var _1=
CALCULATE(
MAX('export log'[export datetime]),
ALL('export log'[export date] , 'export log'[export time])
)
var _2 = NOT(ISBLANK(_1))
var _3 = IF( MAX ( 'export log'[export datetime] ) = _1 ,"YES", "NO")

return
SWITCH( TRUE(),_0 && _2,"YES",_2,_3)

I created a dummy table to be able to use the values as filters (being able to remove the duplicates based on filter and row context). 
var _2 was used to check that I do not get blank values for var _1. 

Quite tricky and still trying to fully grasp the whole build process behind, but if anyone ever needs something like this, there it is.

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Datafruit , Try a measure like

 

Measure =
var _1 = minx(filter(allselected('Export Log'), 'Export Log'[Partner] = max('Export Log'[Partner]) && 'Export Log'[Client_pin] = max('Export Log'[Client_pin]) ), 'Export Log'[Export Date Time])
return
if(max('Export Log'[Export Date Time]) =_1, "Yes", "No")

 

Hi @amitchandak 
I tried your measure, but it expanded the selection to the whole table, ignoring the filters. 

I did though, get some help with the measure and ended up using 4 variables and a dummy tables:


Synt measure =
var _0 = NOT(SELECTEDVALUE('Remove duplicates'[Remove duplicates]) = "Yes")
var _1=
CALCULATE(
MAX('export log'[export datetime]),
ALL('export log'[export date] , 'export log'[export time])
)
var _2 = NOT(ISBLANK(_1))
var _3 = IF( MAX ( 'export log'[export datetime] ) = _1 ,"YES", "NO")

return
SWITCH( TRUE(),_0 && _2,"YES",_2,_3)

I created a dummy table to be able to use the values as filters (being able to remove the duplicates based on filter and row context). 
var _2 was used to check that I do not get blank values for var _1. 

Quite tricky and still trying to fully grasp the whole build process behind, but if anyone ever needs something like this, there it is.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors