Need a help with if

Hi All

Need your help pls. I have two columns col. 1 and col. 2 . I want to create new column using if and trying to reach

if col.2 is "N/A" then check col. 1 , if col.1 starts with "SH" and col.1 is 14 characters then give col.1 else col. 2.

but keep getting an error. if

Need Help

v-yalanwu-msft

5 years ago

Hi, ritanoori ;

According to the picture, maybe your symbol position is not very correct, so you can change dax to the following formula:

SHIPMENT_NUMBER =
IF (
    'Fact_GL'[SH Number] = "N/A",
    IF (AND (
            LEFT ( 'Fact_GL'[Shipment Number], 2 ) = "SH",
            LEN ( 'Fact_GL'[Shipment Number] ) = 14),
        'Fact_GL'[Shipment Number],
        'Fact_GL'[SH Number]))

SHIPMENT_NUMBER2 =
IF (
    'Fact_GL'[SH Number] = "N/A"
        && LEFT ( 'Fact_GL'[Shipment Number], 2 ) = "SH"
        && LEN ( 'Fact_GL'[Shipment Number] ) = 14,
    'Fact_GL'[Shipment Number],
    'Fact_GL'[SH Number])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Need a help with if.pbix29 KB

5 Replies

amitchandak
Super User
5 years ago
ritanoori , Try like

if([col2] = "N/A" && left([Col1],2) = "SH" && len([Col1]) =14 , [Col1], [Col2])
Anonymous
Not applicable
5 years ago
ritanoori

Such a column should be created in Power Query. Not in DAX. One reason for this is optimization and compression.
- ritanoori
  Resolver I
  5 years ago
  Hi Anonymous Thanks for the reply. I agree with you. However, I have one of the columns using lookupvalue which as my knolwedge can't be used in query. Do you suggest better solution?
  
  SH Number = LOOKUPVALUE (
      Fact_TMS_reports[SHIPMENT_NUMBER],
      Fact_TMS_reports[VOUCHER_ID],
      Fact_GL[PoNumber],
      "N/A"
  )
  - Anonymous
    Not applicable
    5 years ago
    Trust me, all you can do in DAX, you can do in Power Query and even more since the M language that PQ uses to do transformations of data is more powerful than DAX. Lookup tables in PQ are not even needed. To obtain what you want you can use joins, for instance, or functions that you then invoke in each row of a table. There are many ways in M/Power Query to achieve what you need.