DAX Help Needed for Complex Data Validation

Question

tamerj1 · Answer

Hi&nbsp;Seeker201&nbsp;
can you provide a couple of examples describing the expected result?

seeker201 · Answer

Hi&nbsp;tamerj1&nbsp;As of now, I don't have a specific expected result for that, but I have provided clear logic for the requirementrelated to malformed addresses in my model, which is based on the customeraddress columnIf you could provide me with the DAX&nbsp; for both requirements, it would be greatly appreciated

tamerj1 · Answer

Seeker201&nbsp;
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.&nbsp;

dangar332 · Answer

hi,&nbsp;Seeker201&nbsp;try below code&nbsp;Malformed Address =IF(CustomerContactQuality[CustomerAddress]&lt;&gt; BLANK() || OR(&nbsp; &nbsp; OR(&nbsp; &nbsp; NOT(CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"1") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"2") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"3") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"4") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"5") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"6") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"7") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"8") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"9") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"0") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],",") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"'") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],".") &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CustomerContactQuality[CustomerAddress],"-")),CONTAINSSTRING(CustomerContactQuality[CustomerAddress]," &nbsp;")),&nbsp; &nbsp; &nbsp; &nbsp; LEN(CustomerContactQuality[CustomerAddress]) &lt;= 10)&nbsp; &nbsp; &nbsp; &nbsp; ,"BAD AD","GOOD AD")and yah! you say about no letter found in the field&nbsp;i don't understand which letter you talk about&nbsp;If this post&nbsp;helps, then please consider&nbsp;Accepting it as the solution&nbsp;to help the other members find it more quickly.

tamerj1 · Answer

Seeker201&nbsp;You have deleted the description. Please let me know if it is a data confidentiality issue.&nbsp;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&nbsp;in manually created tables. That would make the DAX much cleaner.Note: Another method which implies a "Cause &amp; Effect Matrix" which is a pivoted&nbsp;table, may also be used to serve the same purpose but I guess won't be required in this scenario.

Test2 = 
VAR SpecialCharacters = { "~", "@", "#", "$", "%", "^", "*", "(", ")", "_", "-", "+", "=", ":", ";", "'", "\", "/", "|", "{", "}", "[", "]", "?", ",", ".", "&lt;", "&gt;", "!", "`", "  " }
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] &amp; "" )
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 &lt;&gt; BLANK ( )
            VAR ContainsSpecialCharacters = NOT ISEMPTY ( INTERSECT ( SpecialCharacters, T2 ) )
            VAR ContainsNumbers = NOT ISEMPTY ( INTERSECT ( Numbers, T2 ) )
            VAR DoesNotContainLetters = ISEMPTY ( INTERSECT ( Letters, T2 ) )
            RETURN
                IF ( NotEmpty &amp;&amp; ContainsSpecialCharacters, ContainsNumbers || DoesNotContainLetters, FALSE )
        )
    )

Forum Discussion

DAX Help Needed for Complex Data Validation

7 Replies

Related content

Need Help with a Complex DAX Business Requirement

Theme Validation Error

complex visual using AI

Dynamic Complex Filtering with slicers

Validation/optimisation options for AI features