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

 

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!

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