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 solu...
"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" )
    )
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" )
    )
  • kunal_mehta1's avatar
    kunal_mehta1
    Icon for Helper I rankHelper I
    3 years ago

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