Find user who is in two columns

[TB12]

Hello,

I have a table that has columns for groups users should belong to.  They should be mutally exlcusive, but some users belong to both groups by mistake and I'm trying to find who they are.  In the table below, how would I find out that person@gmail.com belongs to both the 'ALL' column and the ' INDIVIDUAL ACCESS' column, both designated by '1's'.  Thanks!

USER_PRINCIPAL_NM	LOCATION ID	ALL	INDIVIDUAL ACCESS
person@gmail.com	XYZ123	0	1
person@gmail.com	XYZ124	0	1
person@gmail.com	XYZ125	0	1
person@gmail.com	ALL_ACCESS	1	0
person2@gmail.com	ABC789	0	1
anotherperson@gmail.com	ALL_ACCESS	1	0

[ThxAlot]

ACCESS.pbix

Simple enough, just unpivot those 2 flag columns; this is a generic solution.

[Ahmedx]

pls try this

or this

[TB12]

That worked!  Thank you!

[rsbin]

@TB12 ,

Here is one potential solution.  I created three additional Calculated Columns as follows:

Flag_ALL = IF( CALCULATE( SUM([ALL]),
                     ALLEXCEPT( UserGroups, UserGroups[USER_PRINCIPAL_NM] )) >= 1,
                     "Yes", "No" )

Flag_Indiv = IF( CALCULATE( SUM([INDIVIDUAL ACCESS]),
                     ALLEXCEPT( UserGroups, UserGroups[USER_PRINCIPAL_NM] )) >=1,
                     "Yes", "No" )
Flag_Both = SWITCH(
                TRUE(),
                AND( [Flag_ALL] = "Yes", [Flag_Indiv] = "Yes"), "Both",
                "" )

You will be able to, at some point, combine these all into 1, but for explanation sake, I felt it best to demonstrate it this way.

USER_PRINCIPAL_NM	LOCATION ID	ALL	INDIVIDUAL ACCESS	Flag_ALL	Flag_Indiv	Flag_Both
person@gmail.com	XYZ123	0	1	Yes	Yes	Both
person@gmail.com	XYZ124	0	1	Yes	Yes	Both
person@gmail.com	XYZ125	0	1	Yes	Yes	Both
person@gmail.com	ALL_ACCESS	1	0	Yes	Yes	Both
person2@gmail.com	ABC789	0	1	No	Yes
anotherperson@gmail.com	ALL_ACCESS	1	0	Yes	No

Hope you will be able to follow the logic.  Reach out if you need further explanation.

Regards,