Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
InsightSeeker
Helper III
Helper III

Need help with DAX

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_nameCodeNumberCode
Card0001001604398XX7614162932
Card0001001604398XXX1614101256
OCCF0001001604398XXX16145105352
OCCF00010016014398XXX1614105938
OCCF0001001604398XXX1614106780
Card0001001604398XXX1614 
Card0001001604398XXX1614111260
Card0001001604398XXX1614

111496

 

Result

 

settle_nameCodeNumberCodeStatus
Card0001001604398XX7614162932Error
Card0001001604398XXX1614101256Valid
OCCF0001001604398XXX16145105352Error
OCCF00010016014398XXX1614105938Error
OCCF0001001604398XXX1614106780Valid
Card0001001604398XXX1614 Error
Card0001001604398XXX1614111260Valid
Cash0001001604398XXX1614111496Error
2 ACCEPTED SOLUTIONS
ToddChitt
Super User
Super User

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.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





View solution in original post

gmsamborn
Super User
Super User

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

 



Proud to be a Super User!

daxformatter.com makes life EASIER!

View solution in original post

2 REPLIES 2
gmsamborn
Super User
Super User

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

 



Proud to be a Super User!

daxformatter.com makes life EASIER!
ToddChitt
Super User
Super User

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.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.