topic Re: DAX Help Needed for Complex Data Validation in DAX Commands and Tips

DAX Help Needed for Complex Data Validation

Seeker201 — Wed, 04 Oct 2023 07:49:19 GMT

Re: DAX Help Needed for Complex Data Validation

tamerj1 — Tue, 03 Oct 2023 17:53:04 GMT

can you provide a couple of examples describing the expected result?

Re: DAX Help Needed for Complex Data Validation

Seeker201 — Tue, 03 Oct 2023 18:16:35 GMT

Hi tamerj1

As of now, I don't have a specific expected result for that, but I have provided clear logic for the requirement
related to malformed addresses in my model, which is based on the customeraddress column

If you could provide me with the DAX for both requirements, it would be greatly appreciated

Re: DAX Help Needed for Complex Data Validation

tamerj1 — Tue, 03 Oct 2023 18:38:53 GMT

Seeker201

I requested an example because I don't have access now to my laptop to open your ssmple file. Perhaps one example would help draft a code without having to look into the file.

Re: DAX Help Needed for Complex Data Validation

Dangar332 — Tue, 03 Oct 2023 19:28:25 GMT

hi, Seeker201
try below code

Malformed Address =

IF(CustomerContactQuality[CustomerAddress]<> BLANK() || OR(

OR(

NOT(CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"1") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"2") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"3") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"4") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"5") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"6") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"7") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"8") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"9") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"0") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],",") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"'") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],".") &&

CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"-")),CONTAINSSTRING(CustomerContactQuality[CustomerAddress]," ")),

LEN(CustomerContactQuality[CustomerAddress]) <= 10)

,"BAD AD","GOOD AD")

and yah! you say about no letter found in the field i don't understand which letter you talk about

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Re: DAX Help Needed for Complex Data Validation

tamerj1 — Wed, 04 Oct 2023 12:19:51 GMT

Seeker201
You have deleted the description. Please let me know if it is a data confidentiality issue.
However, I lost track of the conditions so the following may not be accurate but it describes the general idea on how to perform complex data validation.

I would also recommend to store numbers, letters, special characters in manually created tables. That would make the DAX much cleaner.
Note: Another method which implies a "Cause & Effect Matrix" which is a pivoted table, may also be used to serve the same purpose but I guess won't be required in this scenario.

Test2 = VAR SpecialCharacters = { "~", "@", "#", "$", "%", "^", "*", "(", ")", "_", "-", "+", "=", ":", ";", "'", "\", "/", "|", "{", "}", "[", "]", "?", ",", ".", "<", ">", "!", "`", " " } VAR Letters = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" } VAR Numbers = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "Number", [Value] & "" ) RETURN COUNTROWS ( FILTER ( CustomerContactQuality, VAR String = CustomerContactQuality[CustomerAddress] VAR Length = COALESCE ( LEN ( String ), 1 ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = SELECTCOLUMNS ( T1, "@Letter", MID ( String, [Value], 1 ) ) VAR NotEmpty = String <> BLANK ( ) VAR ContainsSpecialCharacters = NOT ISEMPTY ( INTERSECT ( SpecialCharacters, T2 ) ) VAR ContainsNumbers = NOT ISEMPTY ( INTERSECT ( Numbers, T2 ) ) VAR DoesNotContainLetters = ISEMPTY ( INTERSECT ( Letters, T2 ) ) RETURN IF ( NotEmpty && ContainsSpecialCharacters, ContainsNumbers || DoesNotContainLetters, FALSE ) ) )

Re: DAX Help Needed for Complex Data Validation

ERD — Wed, 04 Oct 2023 14:30:01 GMT

Hello Seeker201 ,

Unfortunately, there is no description in the topic you've provided. Please, add the description, some demo data (a fake one that resembles your real one) and a desired output.

Re: DAX Help Needed for Complex Data Validation

Anonymous — Tue, 10 Oct 2023 01:41:22 GMT

Hi Seeker201,

It seems like you removed the detail requirement from the thread. Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Notice: please not attach sensitive data in shared dummy data.

Regards,

Xiaoxin Sheng