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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.