Not In Common

Greg_Deckler — Wed, 27 Jun 2018 17:27:53 GMT

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

topic Not In Common in Quick Measures Gallery

Not In Common