Validate the data within the same table

[TomLU123]

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 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 ID	Supervisro ID	Validation Result
WS2001	7345382	Valid
WS2002		Invalid
WS2003	WS2001	Valid
WS2004	WS2004	Invalid

Is it possible to create a custom column to achieve that?

Many thanks!

[Zubair_Muhammad · Accepted solution]

@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]

@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]

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 ID	Supervisor ID	Validation Result
WS2001	7345382	Valid
WS2002		Invalid
WS2003	WS2001	Valid
WS2004	WS2004	Invalid
XA1333	WS2001	Valid
MZ2555	XA1333	Valid

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

Many thanks again for your help!

[Zubair_Muhammad]

@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."

MZ2555

XA1333

Valid

[TomLU123]

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 ID	Supervisor ID	Validation Result
WS2001	7345382	Valid
WS2002		Invalid (it is blank)
WS2003	WS2001	Valid (it can be found in the employee ID coulmn)
WS2004	WS2004	Invalid (it is the same as this employee's ID)
XA1333	WS2001	Valid (it can be found in the employee ID coulmn)
MZ2555	BA2111	Invalid (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 · Accepted solution]

@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]

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 ID	Employee ID	Supervisor ID	Validation Result
WS2001	WS2001	7345382	Valid
WS2002	WS2002		Invalid (it is blank)
WS2003	WS2003	WS2001	Valid (it can be found in the employee ID coulmn)
WS2004	WS2004	WS2004	Invalid (it is the same as this employee's ID)
XA1333	XA1333	WS2001	Valid (it can be found in the employee ID coulmn)
3C211	MZ2555	BA2111	Invalid (it can not be found in the employee ID coulmn)
MZ43333	MZ43333	3C211	Valid (it can be found in Legacy ID column)
MZ55555	MZ55555	63C111	Invalid (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 · Accepted solution]

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