Forum Discussion
except in power bi
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 |
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],",")
5 Replies
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.
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],",")