DAX query or a different way

[kunal_mehta1]

Hi all,
I have a column which has all the project numbers which starts with C and starts with O. The few projects that starts with C also has the same last 4 numbers that start with O. I want a solution to have all those O numbers which are similar to the last 4 digits of the C number in front of the C number row and the C numbers which doesn't have same O number would be null.
For example, 

Current format is,

C0001

O0001

O0002

O0002

C0003

C0004

New Format

C0001         O0001

C0002         O0002

C0003         null 

C0004         null

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctily, but please check the below picture and the attached pbix file.

Project new column CC =
VAR _numberpart =
    RIGHT ( Data[Project], 4 )
VAR _Otable =
    FILTER (
        Data,
        LEFT ( Data[Project], 1 ) = "O"
            && RIGHT ( Data[Project], 4 ) = _numberpart
    )
RETURN
    SWITCH (
        TRUE (),
        LEFT ( Data[Project], 1 ) = "O", BLANK (),
        LEFT ( Data[Project], 1 ) = "C", IF ( COUNTROWS ( _Otable ) = 1, MAXX ( _Otable, Data[Project] ), "null" )
    )

[kunal_mehta1]

Thanks @Jihwan_Kim You're a LEGEND. Thanks for your quick response. It worked well.