Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 S...
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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],",")
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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],",")