Forum Discussion
Merging and Filtering related data with unique entries
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 |
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 vResult4. Create matrix using Contractor[Contractor] as rows (this is the Contractor dimension table):
3 Replies
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
Super User
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 vResult4. Create matrix using Contractor[Contractor] as rows (this is the Contractor dimension table):