Help Needed with DAX Formula Logic

[InsightSeeker]

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

[Anonymous]

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.

[rajendraongole1 · Accepted solution]

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!!