cancel
Showing results for
Did you mean:

# Not In Common

Super User
499 Views
Highlighted
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],",")```