DAX Help Needed for Complex Data Validation

[Anonymous]

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

[ERD]

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.

[tamerj1]

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

[Dangar332]

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.

[tamerj1]

Hi @Seeker201 

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

[Seeker201]

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

[tamerj1]

@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.