Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
TomLU123
Helper III
Helper III

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 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 IDSupervisro IDValidation Result
WS20017345382Valid
WS2002 Invalid
WS2003WS2001Valid
WS2004WS2004Invalid

 

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

Many thanks!

 

2 ACCEPTED SOLUTIONS

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

valis.png


Regards
Zubair

Please try my custom visuals

View solution in original post

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

 


Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

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

 

textexctact.png

 

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


Regards
Zubair

Please try my custom visuals

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!

@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

Regards
Zubair

Please try my custom visuals

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

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

valis.png


Regards
Zubair

Please try my custom visuals

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!

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

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.