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

 

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