Need help with DAX

[InsightSeeker]

Hello

I need help to create a new column as per the below condition

If settle name is equal to Card or OCCF &

code is equal to 000100160 &

Number len is 11 &

Number (Left 4) digits are 4398 &

Number (right 4) digits are 1614 &  

code is not blank

then return valid else error.

Data

Table1

settle_name	Code	Number	Code
Card	000100160	4398XX7614	162932
Card	000100160	4398XXX1614	101256
OCCF	000100160	4398XXX16145	105352
OCCF	000100160	14398XXX1614	105938
OCCF	000100160	4398XXX1614	106780
Card	000100160	4398XXX1614
Card	000100160	4398XXX1614	111260
Card	000100160	4398XXX1614	111496

Result

settle_name	Code	Number	Code	Status
Card	000100160	4398XX7614	162932	Error
Card	000100160	4398XXX1614	101256	Valid
OCCF	000100160	4398XXX16145	105352	Error
OCCF	000100160	14398XXX1614	105938	Error
OCCF	000100160	4398XXX1614	106780	Valid
Card	000100160	4398XXX1614		Error
Card	000100160	4398XXX1614	111260	Valid
Cash	000100160	4398XXX1614	111496	Error

[ToddChitt · Accepted solution]

Use the IF ( ) DAX function, along with logical AND ( && ) and IN ( ) operators. Start by writing out the conditions in your native speaking language, which it looks like you have done. Then replace various parts with DAX functions:

"

If settle name is equal to Card or OCCF &

code is equal to 000100160 &

Number len is 11 &

Number (Left 4) digits are 4398 &

Number (right 4) digits are 1614 &  

code is not blank

then return valid else error."

IF ( 'Table'[settle name] IN { "Card", "OCFF" } && 'Table'[code] = "000100160"... , "Valid", "Error" )

I leave it to you, original poster, to fill in the blanks.

[gmsamborn · Accepted solution]

Hi  @InsightSeeker 

Would this help?

Column = 
IF(
    [settle_name] IN { "Card", "OCCF" }
        && [Code] = "000100160"
        && LEN( [Number] ) = 11
        && LEFT( [Number], 4 ) = "4398"
        && RIGHT( [Number], 4 ) = "1614"
        && [Code.1] <> BLANK(),
    "Valid",
    "Error"
)

InsightSeeker.pbix

(I added 2 more lines for testing.)

[ToddChitt · Accepted solution]

Use the IF ( ) DAX function, along with logical AND ( && ) and IN ( ) operators. Start by writing out the conditions in your native speaking language, which it looks like you have done. Then replace various parts with DAX functions:

"

If settle name is equal to Card or OCCF &

code is equal to 000100160 &

Number len is 11 &

Number (Left 4) digits are 4398 &

Number (right 4) digits are 1614 &  

code is not blank

then return valid else error."

IF ( 'Table'[settle name] IN { "Card", "OCFF" } && 'Table'[code] = "000100160"... , "Valid", "Error" )

I leave it to you, original poster, to fill in the blanks.