completion date per user id

[basrooz]

Hi,

I have several registration processes I work with. Some of these processes have 4 steps, some have 3, and some have 10. I have a table with a unique identifier of the registration process and the step, along with a column that tells me if this was the last step in the sequence. The following table shows two registrations (A and B). Registration A contains of 4 steps, of which step 3 is considered the last step. Registration B contains 3 steps of which step 2 is the last one.

Registration step	Is last step
A1	false
A2	false
A3	TRUE
A4	False
B1	False
B2	TRUE
B3	False

I have a table showing me all the steps my users completed. The Registration step is linked to the other registration step table.

Registration step	User ID	Step completed date
A1	XXX	Jan 1 2021
A2	XXX	Jan 2 2021
A3	XXX	Jan 3 2021
A4	XXX	Jan 4 2021
B1	YYY	Jan 1 2021
B2	YYY	Jan 2 2021
B3	YYY	Jan 3 2021
B1	ZZZ	March 1 2021
B2	ZZZ	March 2 2021
B3	ZZZ	March 3 2021

I now want to add a calculated column that shows me for each line of the user what the date was when the last step of the registration got completed. This is what I want to achieve:

Registration step	User ID	Step completed date	Date registration got completed
A1	XXX	Jan 1 2021	Jan 3 2021
A2	XXX	Jan 2 2021	Jan 3 2021
A3	XXX	Jan 3 2021	Jan 3 2021
A4	XXX	Jan 4 2021	Jan 3 2021
B1	YYY	Jan 1 2021	Jan 2 2021
B2	YYY	Jan 2 2021	Jan 2 2021
B3	YYY	Jan 3 2021	Jan 2 2021
B1	ZZZ	March 1 2021	March 2 2021
B2	ZZZ	March 2 2021	March 2 2021
B3	ZZZ	March 3 2021	March 2 2021

I tried the following calculation, but it then only shows me the registration completion date for the line where the registration got completed. 

CALCULATE(
    FIRSTDATE('Main Data'[step completed date]),
    ALLEXCEPT('Main Data','Main Data'[User ID]),
    filter('Registration step',RELATED('Registration step'[is last step])),
)

I need this date to show up for every line of the specic customer, just like in the example.

What would be the right way to achieve this?

Best regards

Bas

[basrooz]

@v-jingzhang Aaah it was driving me mad, and it was so easy to fix! Thanks a lot for looking into this, much appreciated!

Best regards

Bas

[v-jingzhang]

You are welcome😬 Because you use FILTER, it already uses context in registration step table. No need to use RELATED.

Jing

[v-jingzhang · Accepted solution]

Hi @basrooz

Just remove the RELATED in your formula. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.