How to create a a custom Goal chart

Hi all,

Very new to Power BI and trying to show some custom goals.

I have entities (tables) from Dynamics 365 Account, Goals (custom), Model (custom), Opportunity Product, Opportunity

Goals (custom entity, 1:N to Model, 1: N to Account, goal(int), Date)

Model (custom entity,1:N to Product)

Opportunity Product (extra fields(columns), account lookup)

The rationale is to show the Models (20) by Account by month and have the Goal show as a line or dot on the same chart. I also need ability to rollup from Account -> Parent Account -> Territory -> Business Unit.

I have used Lookupvalue to add the Model, Est Close Date, Account Name and Territories to Opportunity Product Table for creating the base data for the chart.

I have attempted various processes to show the Goals however nothing seems to work. My latest attempt was to create a merge query between Opportunity Product and Goal. I created new Custom Columns to convert dates to MM/YYYY which worked well and joined on Account_Value, Model_Value and Date. I then planned to use the index key to pull in the Goal for each record and display it using Max or Average. The issue here however is that it seems to take forever with 16000 records which suggests my method may be flawed and also I need to display all Models whether they have an Opportunity Product or not so with this method wouldn't see the Model or Goal if no Record exists for that month for that Account.

Here is a very basic look at the core tables that need to come together.

Any ideas on how to achieve this?