Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
@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
You are welcome😬 Because you use FILTER, it already uses context in registration step table. No need to use RELATED.
Jing
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |