Not In Common 04-02-2018 17:14 PM - last edited 06-27-2018 10:27 AM

[Greg_Deckler]

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

eyJrIjoiZWQ4ZDJjZGQtOTNkYi00NWI5LThkM2UtZDI1MWE1Yzk1ZmI0IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9