Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 |
Solved! Go to 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.
Proud to be a Super User! | |
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"
)
(I added 2 more lines for testing.)
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"
)
(I added 2 more lines for testing.)
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.
Proud to be a Super User! | |
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |