Forum Discussion

kunal_mehta1's avatar
kunal_mehta1
Icon for Helper I rankHelper I
3 years ago
Solved

DAX query or a different way

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

 

 

"Need Help"
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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" )
    )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    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" )
    )