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

     

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!!

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      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"
)

      • TomLU123's avatar
        TomLU123
        Helper III
        8 years ago

        Hi Zubair_Muhammad,

         

        What if we have additional column that needs to compare. 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 both employee ID column and Legacy ID column, it is invalid.
        • If the supervisor ID is the same as his employee ID, it is invalid.
        • Else are valid. 

        Here is the sample dataset:

        Legacy IDEmployee IDSupervisor IDValidation Result
        WS2001WS20017345382Valid
        WS2002WS2002 Invalid (it is blank)
        WS2003WS2003WS2001Valid (it can be found in the employee ID coulmn)
        WS2004WS2004WS2004Invalid (it is the same as this employee's ID)
        XA1333XA1333WS2001Valid (it can be found in the employee ID coulmn)
        3C211MZ2555BA2111Invalid (it can not be found in the employee ID coulmn)
        MZ43333MZ433333C211Valid (it can be found in Legacy ID column)
        MZ55555MZ5555563C111Invalid (it can not be found in both Legacy ID and Employee ID column)

         

        How should we modify the DAX to achieve it?

        Many thanks for your great help!