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

 

Need Help
  • 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"
)

     

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      TomLU123
      Helper III
      8 years ago

      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!

      • 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