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