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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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

Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.