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

  • TomLU123's avatar
    TomLU123
    Helper III
    8 years ago

    Hi Zubair_Muhammad, 

     

    Thank you so much for your great solution!

     

    I am facing a new challenge that the text in supervisor ID may not be the same. Here is an example:

    Employee IDSupervisor IDValidation Result
    WS20017345382Valid
    WS2002 Invalid
    WS2003WS2001Valid
    WS2004WS2004Invalid
    XA1333WS2001Valid
    MZ2555XA1333Valid

     

    In that case, how should I modify the Query and DAX to achieve it?

    Many thanks again for your help!

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      TomLU123

       

      Just want to understand how this is valid

      as per condition

      "If it contains non-numerical characters and can not be found in the employee ID column, it is invalid."

       

       

      MZ2555XA1333Valid
      • TomLU123's avatar
        TomLU123
        Helper III
        8 years ago

        Hi Zubair_Muhammad,

         

        Sorry for the cofusion! 

         

        Pelase find the condition for Supervisor ID column as below:

        • If it is blank, it is invalid.
        • If it contains non-numerical characters and can not be found in the employee ID column, it is invalid.
        • If the supervisor ID is the same as his employee ID, it is invalid.
        • Else are valid. 

         

        Employee IDSupervisor IDValidation Result
        WS20017345382Valid
        WS2002 Invalid (it is blank)
        WS2003WS2001Valid (it can be found in the employee ID coulmn)
        WS2004WS2004Invalid (it is the same as this employee's ID)
        XA1333WS2001Valid (it can be found in the employee ID coulmn)
        MZ2555BA2111Invalid (it can not be found in the employee ID coulmn)

         

        In this case, how can we modify the Query and DAX to achieve it?

        Many thanks for your help!!