cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
basrooz
Helper I
Helper I

completion date per user id

 

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

 

 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @basrooz

 

Just remove the RELATED in your formula. 

071601.jpg

 

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

View solution in original post

3 REPLIES 3
basrooz
Helper I
Helper I

@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

v-jingzhang
Community Support
Community Support

Hi @basrooz

 

Just remove the RELATED in your formula. 

071601.jpg

 

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

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors