Forum Discussion

kokoro_1202's avatar
kokoro_1202
Icon for Helper I rankHelper I
5 years ago
Solved

help on conditional formatting or nested if

hi all

I have a list of customer id from the system which is not accurate, is it possible to achieve the following with power bi as a new column? Any advice is appreciated.

 

Thank you so much!

Need Help
  • themistoklis's avatar
    themistoklis
    5 years ago

    kokoro_1202 

     

    Try the following formula:

    New Column = 
SWITCH( TRUE(),
LEFT(Table[CustomerID],1) = "w" , Table[CustomerID],
LEFT(Table[CustomerID],3) = "000" , "u" & Table[CustomerID],
LEN(Table[CustomerID]) = 3 , "z" & Table[CustomerID],
"Other"
)

7 Replies

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion
    5 years ago

    kokoro_1202 

     

    Try the following formula:

    New Column = 
SWITCH( TRUE(),
LEFT(Table[CustomerID],1) = "w" , Table[CustomerID],
LEFT(Table[CustomerID],3) = "000" , "u" & Table[CustomerID],
LEN(Table[CustomerID]) = 3 , "z" & Table[CustomerID],
"Other"
)
    • kokoro_1202's avatar
      kokoro_1202
      Icon for Helper I rankHelper I
      5 years ago

      themistoklis 

      how shoulld the formula be to "a" to replace 000 i tried replace but it doesnt work?

      Replace = (Table[CustomerID],1,3,"a"),
      could you advice me again?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi kokoro_1202 

        This might help your requirement 

        Result = If ( Len(Cust[CustomerID]) = 3 , "Z" & Cust[CustomerID] , IF( LEFT(Cust[CustomerID],3) = "000" , "U"&SUBSTITUTE(Cust[CustomerID],"000","a"),Cust[CustomerID]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    1. Create a new column for Result field

    Result = If ( Len(Cust[CustomerID]) = 3 , "Z" & Cust[CustomerID] , IF( LEFT(Cust[CustomerID],3) = "000" , "U"&Cust[CustomerID],Cust[CustomerID]))


    2. Create an another column for font formatting.

    FontId = If ( Len(Cust[CustomerID]) = 3 , 1 , IF( LEFT(Cust[CustomerID],3) = "000" , 2 ,3))
     
    3. Go to format pane and select Value "Result" from Conditional Formatting pane and enable Font color and follow as per the below screenshot