Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
98 | |
90 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |