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 c...

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

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

Forum Discussion

Need a help with if