Forum Discussion

TomLU123's avatar
TomLU123
Helper III
8 years ago
Solved

Validate the data within the same table

Dear Experts,    I wish to building a report to validate the supervisro ID of the employee.  The conditions are like this:  If it is blank, it is invalid. If it contains non-numerical character...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    TomLU123

     

    Please check if attached file helps

     

    Column =
SWITCH (
    TRUE (),
    [Supervisor ID] = ""
        || [Supervisor ID] = [Employee ID]
        || AND (
            [Text in Supervisor ID] <> "",
            NOT (
                CONTAINS (
                    VALUES ( Table1[Text in Employee ID] ),
                    Table1[Text in Employee ID], [Text in Supervisor ID]
                )
            )
        ), "Invalid",
    "Valid"
)

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    TomLU123

     

    In that case, modify as follows

     

    Column =
SWITCH (
    TRUE (),
    [Supervisor ID] = ""
        || [Supervisor ID] = [Employee ID]
        || AND (
            [Text in Supervisor ID] <> "",
            NOT (
                CONTAINS (
                    VALUES ( Table1[Employee ID] ),
                    Table1[Employee ID], [Supervisor ID]
                )
            )
                && NOT (
                    CONTAINS ( VALUES ( Table1[Legacy ID] ), Table1[Legacy ID], [Supervisor ID] )
                )
        ), "Invalid",
    "Valid"
)

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

TomLU123

 

Try these steps

 

First add a custom Column from Query Editor to get the Text in "Supervisor ID"

 

=Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Supervisro ID]),each if Value.Is(Value.FromText(_), type text) then _ else null)))

 

 

Then we can use this calculated Column using DAX

 

Column =
SWITCH (
    TRUE (),
    [Supervisro ID] = ""
        || [Supervisro ID] = [Employee ID]
        || AND (
            [Text in Supervisor ID] <> "",
            [Text in Supervisor ID] <> LEFT ( [Employee ID], 2 )
        ), "Invalid",
    "Valid"
)

Please see file attached