Merging and Filtering related data with unique entries

[Anonymous]

I have 2 data tables with related data, and I'm having trouble relating the data between them.  I am trying to track the planning progress for an upcoming event.  The event scope is broken down on the worklist, which is broken down into jobcards, which are assigned to a primary contractor to plan (table 1).  The primary contractor breaks down a job card into packages, and then the packages are assigned to themselves as well as other contractors (sub contractors) (table2). 

Table 1:

Worklist	Jobcard	...	Primary Contractor
123	ABC	...	A
456	CDE	...	B
789	FGH	...	C
101	IJK	...	A
112	LMN	...	C

Table 2:

Worklist	Jobcard	Package	...	Sub Contractor
123	ABC	ABC.1	...	A
123	ABC	ABC.2	...	B
456	ABC	ABC.3	...	B
456	CDE	CDE.1	....	B
101	LMN	LMN.1	...	C
112	LMN	LMN.2	...	C

These tables are used to track planning progress, so every line on each table is valuable information.    I want to relate the two tables by merging entries based on worklist# and job card number to identify progress in planning, but I also want to maintain the unique entries - which indicate a gap or lack of progress from the respective contractor.  I need to create visual tables that summarize data from both tables (see the visual table for dashboard)  

Visual table for dashboard:

Contractor	Jobcard Count by contractor (Table 1)	Jobcard Count by Contractor (table 2)	Package Count (Table 2)
A	2	1	1
B	1	2	3
C	2	1	2

I've tried establishing relationships (1:1, 1:*, *:*) between the two tables, but I lose the unique entries for one or both of the tables depending on how Power-BI cross filters.  I think I need to create a new calculated table, and establish 1:* relationships to each table.  I'm facing similar struggles when merging the fields into a calculated table (losing items that are unique to each table, and problems when "duplicate" items are in table 2.)  Ideally i'd like to see which scope items have been identified in table 1, but are missing from table 2 and vice versa.  I'm new to power bi so if anyone has another suggestion, let me know. 

Summary Table: 

Worklist	Jobcard
123	ABC
456	ABC
456	CDE
101	LMN
112	LMN
789	FGH
101	IJK

[V-pazhen-msft]

@Anonymous 

We usually create 1 to many relationship, and create measures for those fields that cannot provide expected output. Please provide a full short sample of the 2 tables and the exact expected output from that sample.

Paul Zheng _ Community Support Team

[Anonymous]

Samples of each table are below:

Table 1:

Worklist	Jobcard	...	Primary Contractor
123	ABC	...	A
456	CDE	...	B
789	FGH	...	C
101	IJK	...	A
112	LMN	...	C
131	OPQ	...	D

Table 2:

Worklist	Jobcard	Package	...	Sub Contractor
123	ABC	ABC.1	...	A
123	ABC	ABC.2	...	B
456	ABC	ABC.3	...	B
456	CDE	CDE.1	....	B
101	LMN	LMN.1		C
101	LMN	LMN.2	...	E
112	LMN	LMN.3	...	C

Desired Output table: 

Contractor	Jobcard Count by Primary Contractor (Table 1)	Jobcard Count by Primary Contractor (Counting from Table 2)	Package Count (Table 2)
A	2	1	1
B	1	2	3
C	2	1	2
D	1	0	0
E	0	1	1

The problem with the one to many relationship is that it will filter unique values from one or both of the sample tables: 

* Contractor D was assigned 1 jobcard in table 1, but has not created any packages in table 2. 

* Contractor E is not a primary contractor for any job card on table 1, but they are a subcontractor assigned Package LMN.2 for Jobcard 101-LMN (table 2). 

I am also having trouble counting packages by contractor using measured values - I can only count packages when a specific contractor is the primary contractor and the subcontractor.  

[DataInsights · Accepted solution]

@Anonymous,

This can be solved by creating a Contractor dimension table (star schema).

1. Create a table with a distinct list of all primary contractors and subcontractors. This can be done in Power Query or DAX. Here's the DAX calculated table:

Contractor = 
DISTINCT (
    UNION (
        DISTINCT ( Table1[Primary Contractor] ),
        DISTINCT ( Table2[Sub Contractor] )
    )
)

2. Create relationships:

3. Create measures:

Jobcard Count by Primary Contractor (Table 1) = 
VAR vCount =
    DISTINCTCOUNT ( Table1[Jobcard] )
VAR vResult =
    IF ( ISBLANK ( vCount ), 0, vCount )
RETURN
    vResult

Jobcard Count by Primary Contractor (Table 2) = 
VAR vCount =
    DISTINCTCOUNT ( Table2[Jobcard] )
VAR vResult =
    IF ( ISBLANK ( vCount ), 0, vCount )
RETURN
    vResult

Package Count = 
VAR vCount =
    COUNT ( Table2[Jobcard] )
VAR vResult =
    IF ( ISBLANK ( vCount ), 0, vCount )
RETURN
    vResult

4. Create matrix using Contractor[Contractor] as rows (this is the Contractor dimension table):