topic Re: except in power bi in Report Server

except in power bi

Anonymous — Mon, 02 Apr 2018 01:14:42 GMT

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

Re: except in power bi

Greg_Deckler — Mon, 02 Apr 2018 12:35:36 GMT

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.

Re: except in power bi

Anonymous — Mon, 02 Apr 2018 18:13:05 GMT

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.

Re: except in power bi

Greg_Deckler — Mon, 02 Apr 2018 19:23:14 GMT

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

Re: except in power bi

Anonymous — Mon, 02 Apr 2018 22:53:09 GMT

Thanks Greg.

Re: except in power bi

Greg_Deckler — Tue, 03 Apr 2018 00:17:11 GMT

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