Hey everyone,
I have been asked to provide a graphic that shows metrics for each manager’s/partner’s staff who report to them, this one is easy, but drums up the problem as each manager/partner wants to see themselves in their metrics but they have a manager.
My data looks like this:
Can anyone think of a way to compare the Partner ID column with the Staff ID column to create a custom column that says essentially if a Partner ID is found at all in the Staff ID column, then return the Staff ID otherwise return the Partner ID?
Thanks!
@HennemanTJ
The IN operator makes this a snap. Here’s the DAX for the calculated column:
Check ID =
VAR StaffTable =
DISTINCT( Data[Staff ID] )
VAR CompareIDs =
IF(
Data[Partner ID] IN StaffTable,
Data[Staff ID],
Data[Partner ID]
)
RETURN CompareIDs
(I also threw in a bonus measure to color the ones found red).
Color Duplicates Red =
SWITCH( TRUE(),
SELECTEDVALUE( Data[Partner ID]) = SELECTEDVALUE( Data[Staff ID] ), "Red",
CONTAINSSTRINGEXACT(SELECTEDVALUE( Data[Partner ID] ), SELECTEDVALUE( Data[Check ID] )), "Black",
"Red"
)
Here’s what it looks like pulled together:
And here’s a great article detailing the uses of the IN operator:
I hope this is helpful. Full solution file attached.
3 Likes
Hey @BrianJ ,
Great solution, for some reason it’s not working in my PBIX, it is always returning the Staff ID… but you’re getting to the heart of what I’m trying to get to… If a someone (StaffID) is listed as a partner for someone else they need to show as their own partner. But that IN operator is super helpful.
@HennemanTJ,
One suggestion as to why it may not be working in your PBIX. If you have any leading or trailing spaces in your data, the string comparison in “IN” may not return the correct results. You can try going into Power Query and executing a TRIM transformation to remove those possible spaces, and see if that then returns the correct results.
If not, if you want to post your PBIX, I’d be glad to take a further look at it to see why it’s not returning the correct results.
Very useful article link @BrianJ .
Thanks for sharing it!
Hi @HennemanTJ,
Seems like an excellent job for Power Query to me 
So here’s an alternative…
- In yellow the first test let’s you exclude empty values
- In green the main logic, include the previous step name from the applied steps pane before entering the column reference as first parameter in List.Contains
- the if statement let’s you return the value of your choice when true / false…
I hope this is helpful.
1 Like
Both of these are solutions, thanks @Melissa and @BrianJ
@HennemanTJ,
Yup, both of these will work just fine. However, looking at them side-by-side I actually think @Melissa’s solution is better. From both a simplicity and a performance standpoint, I think Power Query is the right tool for this job.
