Cluster Chart between two tables - displaying x axis values where only one table is populated

[jukeysmoot]

Apologies for the wording of the title - having a hard time describing my need here. 

Essentially, I have a table that takes acccounting data from our ERP system, and a separate table where I have budget values inputted on a weekly basis. Using a separate reference date chart, I can display Weekly cost data vs. our budgeted costs for each week, which are displayed across an x-axis. 

my trouble is displaying budgeted values for weeks where there is no accounting data entered yet (future dates). I'd like to be able to display budget figures for future dates so management can see what their budget is upcoming. However, because the "week date" doesn't exist in our accounting table yet, even though it exists in my reference table and my budget table, Power BI doesn't seem to want to display my budget totals for that week.

^ in the picture here, I have values entered into a budget table for the blank dates, they're just not showing since we haven't received any cost data in the other table as of yet.

Table structure (simpilified)

Accounting Table (cost data up to current date) *(many) <--------- (one) Date Table (job week, where all dates in a week are assigned to a date (one) ---------> * (many) Budget Table (budget data for entire length of project, in the above picture's case, about 12 weeks that are not displayed.

Apologies for any vagaries, I'm having a tough time iterating the exact issue! Hopefully I made it clear enough.

[Anonymous]

Hi @jukeysmoot 

Due to I don’t know your data model, I build three tables and build relationships as the information you provided.

I can see the budge in my test result whether I build the visual by columns or measures. Please provide me your data model or share you pbix file with me by your onedrive for business.

Date table：

Budge Table：

Cost Table：

I try to build a Line and clustered column chart by two ways.

Cost column and Budge column:

Measures:

Budge Cost = CALCULATE(SUM(Budge[Budge]),FILTER('Date',MAX(Budge[Date])='Date'[Job day]))

Week Job cost = CALCULATE(SUM(Cost[Cost]),FILTER('Date',MAX(Cost[Date])='Date'[Job day]))

You can download the pbix file from this link: Cluster Chart between two tables

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[tex628]

The relationship between the accounting table and the date table, which crossfilter direction does it have?

/ J

[jukeysmoot]

single, date to accounting, one to many

[tex628]

In your graph, the x-axis dimension "Week date", does it come from the accounting table or the reference date table? 

/ J

[jukeysmoot · Accepted solution]

It came from the date table. 

But you were on the right path with the cross-filter direction. For simplicity's sake, I didn't mention the multitude of other tables in this dataset. It was a relationship direction problem with a separate reference table that was messing things up.

For anyone that stumbles upon this troubleshooting their own concern: For some reason, I had 'accounting''s relationship to a separate 'job info' table (reference with information about projects) set as both ways cross filter, and my 'budget' table also has a relationship with that table due to common job numbers. So even though relationships were correct on the 'date' table, the cross-filter relationship on that separate reference table was allowing the 'accounting' table to filter the 'budget' table's date values for the visual. When I switched that to one direction relationship 'job info' (one) ----> 'accounting' (many), the visual was instantly fixed.

Thanks so much for the responses and getting me down the right track.

[tex628]

I'm happy to hear that you managed to solve it! Double sided relationships can cause quite alot of issues and It's not always so easy to grasp just how they affect the datamodel. On the other hand they can allow you to do certain things that simply are impossible with single sided. 🙂

Br,
J