Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

ProjectBrandSubBrand
P1B1S1
P2B1S2
P3B1S3
P4B1S2
P5B1S2
P6B1S1

 

Employee Table:

ProjectEmployee
P1E1
P1E2
P2E3
P2E4
P3E1
P4E2
P5E3
P6E7
1 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],",")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

 

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks Greg.  

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors