Need a help with if

[ritanoori]

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 

[v-yalanwu-msft · Accepted solution]

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

or 

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.

[Anonymous]

@ritanoori 

Such a column should be created in Power Query. Not in DAX. One reason for this is optimization and compression.

[ritanoori]

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]

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.

[amitchandak]

@ritanoori , Try like

if([col2] = "N/A" && left([Col1],2) = "SH" && len([Col1]) =14 , [Col1], [Col2])