How to create a new column like a self-join in SQL?

[OceanExplorer]

Hello, I have this original data table T1 using DirectQuery mode:

I want to create a table visual to show for the same ID, when Action is 'End', who is the Staff that previously 'Start' OR 'Transfer', probably need to create a new column - Started Staff, the expected result as below:

[MarkLaf · Accepted solution]

Assuming the blanks in T1[Staff Name] are nulls, you can use the following measure:

Started Staff = 
CALCULATE(
    FIRSTNONBLANK( T1[Staff Name], T1[Staff Name] ),
    ALL( T1[Action] )
)

In a table where you set filter of T1[Action] = End:

[Anonymous]

Hi @OceanExplorer ,
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.
Thank you.

[Anonymous]

Hi @OceanExplorer ,

I wanted to check and see if you had a chance to review our previous response. Please let me know if everything is sorted or if you need any further assistance.

Thank you.

[Anonymous]

Hi @OceanExplorer ,
Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

[Anonymous · Accepted solution]

Hi @OceanExplorer ,
Thank you @MarkLaf for the helpful response!

I tried to recreate it on my local with the sample data.So that I used below measure to create a calculated column:

Started Staff =

VAR CurrentID = T1[ID]

VAR StaffName =

    CALCULATE(

        LASTNONBLANK(T1[Staff Name], 1),

        FILTER(

            T1,

            T1[ID] = CurrentID &&

            T1[Action] IN {"Start", "Transfer"}

        )

    )

RETURN

    IF(T1[Action] = "End", StaffName)

Here is the screenshot and pbix for your reference:

If this solution meets your requirement,consider accepting it as solution.

Thank you for being a valued member in Microsoft Fabric Community Forum!

Regards,
Pallavi.

[MarkLaf · Accepted solution]

Assuming the blanks in T1[Staff Name] are nulls, you can use the following measure:

Started Staff = 
CALCULATE(
    FIRSTNONBLANK( T1[Staff Name], T1[Staff Name] ),
    ALL( T1[Action] )
)

In a table where you set filter of T1[Action] = End: