Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
Dear Experts,
I wish to building a report to validate the supervisro ID of the employee.
The conditions are like this:
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!
Solved! Go to Solution.
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" )
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" )
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
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!
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 |
Hi @Zubair_Muhammad,
Sorry for the cofusion!
Pelase find the condition for Supervisor ID column as below:
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!!
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" )
Hi @Zubair_Muhammad,
What if we have additional column that needs to compare. Pelase find the condition for Supervisor ID column as below:
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!
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" )
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
59 | |
58 |