Get Rank based on Event and grouped by ID

[EduBubicz]

Hey guys,

I really need some help with DAX;

I have a database and I want to set a user [Rank] that would be reset based on specific [Event] and [Event Date], for each [ID].

Below are some examples of the expected results. For each "ENV" event, the rank counting is reset.

Any Ideas?

thanks in advance

ID	Event	Event Date	User	Rank (Expected Result)
8189576	ENV	29/10/2020 19:20
8189576	REVI	30/10/2020 08:02	alema	1
8189576	ENV	03/11/2020 11:43
8189576	AP	03/11/2020 11:44	alema	1
8189576	AP	03/11/2020 14:03	jflor	2
7940462	ENV	14/09/2020 14:39
7940462	AP	17/09/2020 08:50	tcosta	1
7940462	REVI	21/09/2020 11:06	msantos	2
7940462	ENV	24/09/2020 13:45
7940462	AP	28/09/2020 10:39	rjunior	1
7940462	AP	29/09/2020 08:35	slima	2
7919385	ENV	01/09/2020 14:56
7919385	AP	01/09/2020 16:26	tjung	1
7919385	REVI	04/10/2020 14:39	tjung	2
7919385	ENV	08/10/2020 09:59
7919385	AP	03/11/2020 16:11	rsantos	1
7919385	AP	06/11/2020 11:09	ejunior	2
7919359	ENV	03/09/2020 15:01
7919359	REVI	09/09/2020 09:53	rcjunior	1
7919359	ENV	09/09/2020 15:44
7919359	REVI	09/09/2020 16:14	rcjunior	1
7919359	ENV	10/09/2020 09:00
7919359	AP	10/09/2020 09:15	rcjunior	1
7919359	AP	11/09/2020 07:05	epombo	2
7919359	AP	14/09/2020 09:13	esouza	3
7919411	ENV	01/09/2020 11:17
7919411	AP	01/09/2020 11:24	csrego	1
7919411	AP	08/09/2020 14:14	csrego	2
7919411	AP	16/09/2020 15:43	eneto	3

[mahoneypat]

Please try this calculated column expression that seems to get your desired results.

Rank Measure =
VAR vThisDT = Events[Event Date]
VAR vThisID = Events[ID]
VAR vLastEvent =
    CALCULATE (
        MAX ( Events[Event Date] ),
        Events[Event] = "ENV",
        Events[Event Date] < vThisDT,
        Events[ID] = vThisID,
        ALL ( Events )
    )
VAR vRowsBefore =
    CALCULATE (
        COUNTROWS ( Events ),
        ALL ( Events ),
        Events[Event Date] <= vThisDT,
        Events[Event Date] > vLastEvent,
        Events[ID] = vThisID,
        ALL ( Events )
    )
RETURN
    IF (
        Events[Event] = "ENV",
        BLANK (),
        vRowsBefore
    )

Regards,

Pat

[EduBubicz]

Thansks for your answer @amitchandak , but I couldnt get the results as expected.
Here is the table with the results using your first suggestion (for the second, the results were the same).

As you can see, the rank didn't reset for each "ENV" event, and isn't blank when there are no users.

Any other suggestions? 
thanks in advance !

[amitchandak]

@EduBubicz , Create a new column like

rankx(filter(table, [ID] = earlier([ID]) && not(isblank([User]))), [Event Date],,desc,dense)

or

if(not(isblank([User])), rankx(filter(table, [ID] = earlier([ID]) && not(isblank([User]))), [Event Date],,desc,dense) , blank())

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...