except in power bi

[Anonymous]

Hi,

I have a requirement to find list of employee ids similar to except in venn diagram. Project table has relationship with employee table based on column project. If the user selects SubBrand S1 and then S2 from a slicer then result has to S1 Left anti S2. In the below example employee E7 would be the result. If the user selects S2 first and then S1 the result has to be S2 left anti S1 i.e employee E3. I am unable to capture first selection and second selection in a same slicer. So, I copied the Project table and used SubBrand on left side and SubBrand from copied table on right side.  But except dax function is giving empty result though i am selecting S1 on left and S2 on right.  Any suggestions are most welcome. Thanks!

Project Table

Project	Brand	SubBrand
P1	B1	S1
P2	B1	S2
P3	B1	S3
P4	B1	S2
P5	B1	S2
P6	B1	S1

Employee Table:

Project	Employee
P1	E1
P1	E2
P2	E3
P2	E4
P3	E1
P4	E2
P5	E3
P6	E7

[Greg_Deckler]

Seems that there are multiple issues here but I am not exactly understanding what you are trying to do or why E7 would be the result. Is that because E7 is the only person that is on an S1 project but not an S2 project? I've done some interesting things with anti-slicers and the like in the Quick Measure Gallery.

[Anonymous]

Yes Greg. I have selected S1 in the left side slicer and S2 in the right side slicer.  E7 is the only person that is on an S1 project but not an S2 project. Let me know how to perform using dax or alternate way.

[Greg_Deckler · Accepted solution]

I was able to do it this way, although with your data and your conditions, I get E1 and E7 as both of them are on S1 projects but not S2 projects.

Measure 7 = 
VAR tableA = CALCULATETABLE(DISTINCT(Employees[Employee]),ALLEXCEPT(Employees,Projects[SubBrand]))
VAR tableB = CALCULATETABLE(DISTINCT(Employees[Employee]),ALLEXCEPT(Employees,Projects2[SubBrand]))
VAR results = EXCEPT(tableA,tableB)
RETURN CONCATENATEX(results,[Employee],",")

[Anonymous]

Thanks Greg.  

[Greg_Deckler]

Happy to help, I posted the solution with PBIX file in the Quick Measures Gallery here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Not-In-Common/m-p/388273#M118