Table relationships with a Master date table

[jjohnson3922]

I have three tables

1. All Invoices - All Invoices from inception, including a Invoice Date collumn

2. Daily Stock Quantity - A Daily snapshot of our on hand quantity for each part

3. Callendar Table - Generated

We are trying to plot a line chart that shows the number of parts sold on a specific day, and the number of on hand we had on that day. We also would like to see all relevant invoices. 

I have a one to many relationship between the Callendar table and the other two tables. 

 I have 4 visuals, a slicer, two tables, and a line chart. The slicer is used to allow the user to select a single part number, and the other 3 visuals are to update accordingly. As you can see, when two of the remaining three are working as expected, but one of the table visuals is not functioning as expected. 

If you look you'll see that the invoices table is not reflecting the selected part.

I am realitivily new to Power BI, so please excuse my ignorance but i'd assume i'd need another relationship between the all invoices table and the stock Daily Stock Quantity table on the part collumn, but when I try to do this I get an inactive relationship. 

Am I even on the right track or should I be approaching this in a different way? Any help would be appriciated! 

Thank you!!

[rsbin · Accepted solution]

@jjohnson3922,

I am by no means an expert, but I think what you need is a DIM PartMaster table.  You could then join this table to your DailyStock table (1:many) and as well to your Invoice table (again a 1:many). 

Then use PartNumber from this new Dimension table and your charts should react accordingly.

Good Luck and regards,

[jjohnson3922]

Just want to make sure I understand. 

I need to create a reference table that houses the list of parts and then link the all invoices table and the daily stock quantity table to this reference table? Like below? 

Then use the PartMaster Part as my slicer list?

[rsbin]

@jjohnson3922 

Yup...you got it.

Best Regards,

[jjohnson3922]

Awesome! Thank you! I will try this out if i can get it to work.

When I attempt to create the relationship by dragging part to part my Power BI desktop locks up and eventually crashes. 

[rsbin]

@jjohnson3922,

Unfortunately, crashes are out of my area of expertise.  Hope you can get that issue resolved.