Linking Tables without losing unmatched records

[Jackopoe]

Hi all,

I have two tables, one containing Actual data for Month and Customer Code and one containing Target values for Month and Customer Code. 

I am trying with no luck to join them in a way that keeps all of the codes and links the target only for the matching ones.
I have created a key that concatenates code and month BUT

• The following is the result I get if I set the key-relationship to filter from Target to Actual.
  

  

• This is the result when filtering from Actual to Target
  
  
  Below is the desired output where all codes have the matching target:

As an additional note, actuals table is very heavy (about 4 millions) so a heavy-computation solution could not work. 

Link to the example file

Thank you in advance for your help!

Kind Regards

Jenna 😘

[PaulDBrown]

@Jackopoe 

The recommended best pratice is to set up your model with dimension or lookup tables to use in slicers, filters or measures. These lookup tables allow you to establish the relevant filter context for equivalent values in your tables.

Here is an example of your model with a "Code" lookup table linked to both your actuals and target tables through "code" as the  common field:,

 In order to set it up this way, there are a few methods. Let me walk you through one of these.

Go into Power Query and:

1) Select your "code" column in your either of your tables (Actuals or Target), right click on it and select "Add as new query" (towards the bottom of the list)

2) Convert this query to a table (and give it a name)

3) to ensure that you have all possible values for code, repeat this excercise with the other table.

4) now append both these tables into a new one.

This creates a new query/table with all the possible values for "Code"  from both your tables.

5) remove duplicate rows from this new table and give it a name (I've called it Code)

6) since you will not be needing the two tables you created previously, you can disable loading them into the model by right clicking on each and de-selecting "Enable load".

7) Carry out this process again for you Month columns in both your tables to create a new query with the unique values for month.

Load your queries into the model, and set up the one-to-many relationships between your newly created tables and your Actuals and Target tables by linking the corresponding columns. Your model should lool like this:

Now you can set up your visuals using the fields from your lookup tables as your rows

And you get the following:

Edit: Something I haven't metioned is that it is also highly recomended to have a Calendar/Date table with unique dates covering the range of dates in you model (and use it as a lookup table in slicers, measures or filter for anything to do with dates). In "99%" of models, this is the way to do it (unless a model has no dates!)

In the example I've provided, I've created the "Month" lookup table; it is much better to create a Date table instead of the month lookup table.

[Tad17]

Hey @Jackopoe 

Can you share the measures and data relationships going into these tables? It looks like there may be an issue with the Target.

Also, you will need to select "Show Items with no Data " to get the desired output: https://docs.microsoft.com/en-us/power-bi/desktop-show-items-no-data

[Jackopoe]

Hi Tad17, I have posted a link to the example file.