Hi there, new to the community and trying to build my first model.
I keep getting a problem where I get a data loop. I have read up about this and understand the issue but cannot seem to get my head around how to solve the problem.
I have 3 tables:
TABLE 1: [CUSTOMER_HIGHLANDER]
MS SQL Table with dimensions:
TABLE 2: [HIGHLANDER_FINANCE]
Also a MS SQL table with measures
,[INFORMATION_DATE] (This is monthly data so as at month end)
I created a concatenated field on CUSTOMER_KEY and ACCOUNT_NUMBER (CUST_ACCNT_NUMBER) and joined these 2 tables. That was no problem.
These SQL tables show the situation as is. The part I am struggling with is to bring in the 3rd table (BUDGET_DATA) which shows the budgetted figures in XLSX format. Eg I want to be able to show number of accounts budgeted vs actual. Or Non-interest-income budgeted vs actual.
The fields in this data are:
|# of Clients|
|# of Accounts|
|# of Accounts Retail Transactional NIR|
|Ave Debit Balance|
|Ave Credit Balance|
This data is aggregated on a high level by month, product and customer_segment. Unfortunately the product and segment don't match up with any fields on the existing SQL tables.
So what I elected to do was to create a PRODUCT_MAPPING and SEGMENT_MAPPING column on the [CUSTOMER_HIGHLANDER] data. These columns create a PRODUCT that matches up with the PRODUCT_MAPPING on BUDGET_DATA and SEGMENT that matches up with the SEGMENT_MAPPING on BUDGET_DATA. But this data is also aggregated by MONTH.
When I try to bring this data into the model, I get an error saying I have a loop.
I am really struggling to figure out how to structure my data so I can show for example by date by product showing number of customers. I'd appreciate any tips or suggestions!