cancel
Showing results for 
Search instead for 
Did you mean: 

Not In Common

Super User
584 Views
Highlighted
Super User
Super User

Not In Common

Imagine that you have a data set in which you wish to find data elements that belong to one set but not to another. This is the goal of the "Not In Common" quick measure.

 

Suppose that you have data tables such as the following:

 

Employees

Project    Employee

P1 E1
P1 E2
P2 E3
P2 E4
P3 E1
P4 E2
P5 E3
P6 E7

 

Projects

Project     Brand    SubBrand

P1 B1 S1
P2 B1 S2
P3 B1 S3
P4 B1 S2
P5 B1 S2
P6 B1 S1

 

Projects2

Project    Brand     SubBrand

P1 B1 S1
P2 B1 S2
P3 B1 S3
P4 B1 S2
P5 B1 S2
P6 B1 S1

 

Relationships exist between these tables based upon the Project columns:

Projects ->* Employees *<-1 Projects2

 

We wish to find Employees involved in projects of one SubBrand but not another. We can create two slicers based upon Projects[SubBrand] and Projects2[SubBrand] and create the following measure:

 

 

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

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!