Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
04-02-2018 17:14 PM - last edited 06-27-2018 10:27 AM
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