Forum Discussion
Help Needed with DAX Formula Logic
Hello
I have created a DAX formula as shown below, but I am having a slight issue with the logic.
I need the result to be either valid or error only for the customer (_Customer) specified in the DAX. For all other customers, it should return true. Is there a way to do this?
Validation =
var _left = LEFT('TABLE_1'[Card_Number],4)
var _right = RIGHT('TABLE_1'[Card_Number],4)
var _len = LEN('TABLE_1'[Card_Number])
var _customer = 'TABLE_1'[fop_customer_code]
var _auth = 'TABLE_1'[Card_Auth_Code]
var _mode = 'TABLE_1'[settle_name]
var _tktdate = 'TABLE_1'[Ticket_Date]
RETURN if(
_mode = "Card" || _mode = "OCCF" && _customer = "0000103900" && _len =15 && _left = "3744" && _right = "9380" && _auth <> BLANK() ||
_tktdate > DATE(24,02,01) && _mode = "Card" || _mode = "OCCF" && _customer = "0000103902" && _len =15 && _left = "3744" && _right = "9380" && _auth <> BLANK() ||
_tktdate < DATE(24,01,31) && _mode = "Card" || _mode = "OCCF" && _customer = "0000103902" && _len =15 && _left = "3744" && _right = "1031" && _auth <> BLANK() ||
_mode = "Card" || _mode = "OCCF" && _customer = "0000107500" && _len =15 && _left = "3744" && _right = "3912" && _auth <> BLANK() ||
_mode = "Card" || _mode = "OCCF" && _customer = "0000107501" && _len =15 && _left = "3744" && _right = "3912" && _auth <> BLANK() ||
,"Valid","Error")
Hi InsightSeeker - I have modified a slight change in condition as per customer code is in specified list , can you try below
Validation =
var _left = LEFT('TABLE_1'[Card_Number], 4)
var _right = RIGHT('TABLE_1'[Card_Number], 4)
var _len = LEN('TABLE_1'[Card_Number])
var _customer = 'TABLE_1'[fop_customer_code]
var _auth = 'TABLE_1'[Card_Auth_Code]
var _mode = 'TABLE_1'[settle_name]
var _tktdate = 'TABLE_1'[Ticket_Date]RETURN
IF(
_customer IN {"0000103900", "0000103902", "0000107500", "0000107501"},
IF(
(_mode = "Card" || _mode = "OCCF") && _customer = "0000103900" && _len = 15 && _left = "3744" && _right = "9380" && _auth <> BLANK() ||
_tktdate > DATE(2024, 02, 01) && (_mode = "Card" || _mode = "OCCF") && _customer = "0000103902" && _len = 15 && _left = "3744" && _right = "9380" && _auth <> BLANK() ||
_tktdate < DATE(2024, 01, 31) && (_mode = "Card" || _mode = "OCCF") && _customer = "0000103902" && _len = 15 && _left = "3744" && _right = "1031" && _auth <> BLANK() ||
(_mode = "Card" || _mode = "OCCF") && _customer = "0000107500" && _len = 15 && _left = "3744" && _right = "3912" && _auth <> BLANK() ||
(_mode = "Card" || _mode = "OCCF") && _customer = "0000107501" && _len = 15 && _left = "3744" && _right = "3912" && _auth <> BLANK(),
"Valid",
"Error"
),
"True"
)the above logic returns , if the customer code matches any specified values, it returns valid or error
if the customer code doesnot matches to go to the true loop and returns.
Try the above one statement.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
2 Replies
Hi InsightSeeker - I have modified a slight change in condition as per customer code is in specified list , can you try below
Validation =
var _left = LEFT('TABLE_1'[Card_Number], 4)
var _right = RIGHT('TABLE_1'[Card_Number], 4)
var _len = LEN('TABLE_1'[Card_Number])
var _customer = 'TABLE_1'[fop_customer_code]
var _auth = 'TABLE_1'[Card_Auth_Code]
var _mode = 'TABLE_1'[settle_name]
var _tktdate = 'TABLE_1'[Ticket_Date]RETURN
IF(
_customer IN {"0000103900", "0000103902", "0000107500", "0000107501"},
IF(
(_mode = "Card" || _mode = "OCCF") && _customer = "0000103900" && _len = 15 && _left = "3744" && _right = "9380" && _auth <> BLANK() ||
_tktdate > DATE(2024, 02, 01) && (_mode = "Card" || _mode = "OCCF") && _customer = "0000103902" && _len = 15 && _left = "3744" && _right = "9380" && _auth <> BLANK() ||
_tktdate < DATE(2024, 01, 31) && (_mode = "Card" || _mode = "OCCF") && _customer = "0000103902" && _len = 15 && _left = "3744" && _right = "1031" && _auth <> BLANK() ||
(_mode = "Card" || _mode = "OCCF") && _customer = "0000107500" && _len = 15 && _left = "3744" && _right = "3912" && _auth <> BLANK() ||
(_mode = "Card" || _mode = "OCCF") && _customer = "0000107501" && _len = 15 && _left = "3744" && _right = "3912" && _auth <> BLANK(),
"Valid",
"Error"
),
"True"
)the above logic returns , if the customer code matches any specified values, it returns valid or error
if the customer code doesnot matches to go to the true loop and returns.
Try the above one statement.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Your solutions is great rajendraongole1 , I tested your DAX expression with test data and it worked fine. I've optimized the DAX expression you provided.
Hi, InsightSeeker
Here's the test data I used:
Here are the optimized DAX expressions:
Validation1 = var _left = LEFT('TABLE_1'[Card_Number],4) var _right = RIGHT('TABLE_1'[Card_Number],4) var _len = LEN('TABLE_1'[Card_Number]) var _customer = 'TABLE_1'[fop_customer_code] var _auth = 'TABLE_1'[Card_Auth_Code] var _mode = 'TABLE_1'[settle_name] var _tktdate = 'TABLE_1'[Ticket_Date] RETURN if( NOT _customer IN {"0000103900","0000103902","0000107500","0000107501"} ,"TRUE", if( _mode IN {"Card" , "OCCF"} && _len = 15 && _left = "3744" && _auth <> BLANK() && ( _customer = "0000103900" && _right = "9380" || _tktdate > DATE(24,02,01) && _customer = "0000103902"&&_right="9380"|| _tktdate < DATE(24,02,01) && _customer = "0000103902"&&_right="1031" || _customer = "0000107500" && _right = "3912" || _customer = "0000107501" && _right = "3912" ) ,"Valid","Error") )TURE is returned when the customer is not in the account that you define. Here are the results:
I've provided the PBIX file used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
