Fill out a row map weekly where row info and completed info are two different data tables

[cnor152]

Apologies for the title - hard to explain and I am relatively new to PowerBi. 

I have 2 tables and am trying to create 1 dashboard. 

Table 1 is a map of rows in our sites. This does not change ever, at least until we build more sites. 

Table 2 is data that is updated every day - when the user finishes a row they mark it complete (1). If it is anything that isn't 1, it's not complete. 

The dashboard I am trying to create is something like this, where there are 3 slicers (showing the same dashboard with just different filter settings). Important requirement is that all the rows are always shown, and they are just marked complete when done.

I've tried for a long time to get this work. Only solution I found was creating a row map (Table 1), but for every week and every job. Even doing that, I could not get the site filter to work. 

I'm hoping there might be a simple solution, and I just don't understand relationships across tables?

(If it helps, I can change the row numbers to be unique - i.e "NY-001")

Here is the data:

Row	Site
1	New York
2	New York
4	New York
5	New York
6	New York
7	New York
8	New York
9	New York
10	New York
1	London
2	London
4	London
5	London
6	London
7	London
8	London
9	London
10	London
11	London
12	London

Name	Week	Job	Site	Row	Complete
Adam	1852	Sweeping	New York	1	1
Adam	1852	Sweeping	New York	2	1
Adam	1852	Sweeping	New York	3	1
Adam	1852	Sweeping	New York	4	1
Adam	1852	Maintenance	New York	1	1
Adam	1852	Operating	New York	6	1
Adam	1852	Operating	New York	7	1
Adam	1852	Operating	New York	8	1
Adam	1852	Operating	New York	9	1
Adam	1852	Operating	New York	10	2
Jane	1852	Maintenance	London	1	1
Jane	1852	Maintenance	London	2	1
Jane	1852	Maintenance	London	3	1
Jane	1852	Maintenance	London	4	1
Jane	1852	Maintenance	London	5	1
Jane	1852	Operating	London	6	2
Jane	1852	Operating	London	7	2
Jane	1852	Operating	London	8	2
Jane	1852	Operating	London	9	2
Jane	1852	Operating	London	10	2
Adam	1901	Maintenance	New York	1	2
Adam	1901	Sweeping	New York	2	1
Bob	1901	Sweeping	New York	4	1
Jane	1901	Sweeping	Londan	10	1

[parry2k · Accepted solution]

@cnor152 solution is attached, did transform some data, probably it is all self-explanatory, if there is any question, please ask.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[cnor152]

This is awesome, thanks so much man! 

The only tiny thing that isn't a big deal - can the slicer for jobs only show those that were done? 

For example if there are 100 possible jobs, but only 20 were done for week 1 (based off the data table), can the slicer just show the 20? Not the 100 is currently does?