Search code examples
powerbidaxdatamodel

Power BI data model not correlating properly

I'm trying to build some metrics with Power BI around customer complaint data. I'm pulling two datasets (Stage1data and Stage2data) from an Excel spreadsheet. The goal is to create the same metrics that have been created in the Excel spreadsheet.

I've also built a date table using DAX and connected the three tables as shown below:

In Model view

Stage1Data and Stage2Data are connected using the column "Reference". The Date relationship is as follows:

'Stage1Data'[Date] -<- 'Date'[Date] ->- 'Stage2Data'[Escalation date]

I've then created some measures such as:

Stage 1 Complaints = COUNT(Stage1Data[Reference])

Stage 2 Complaints = COUNT(Stage2Data[Reference])

Total Complaints = [Stage 1 Complaints] + [Stage 2 Complaints]

The problem is the complaints are being correlated to another column 'Submitted', rather than 'Escalation date'/ See below:

The total complaints is showing as 85, when it should be 77. This is because the Stage 2 data is correlating with the 'submitted' column, rather than the 'escalation date' column.

A side note, the relationship from 'Date' to 'Stage2Data' is inactive. When I try to make this active, the following text box appears:

I've tried a couple things, such as creating a surrogate key for each dataset and replace the reference for the key in the DAX queries, but I still get the same issue.

I created the date table to try to overcome this but it didn't make a difference.

My assumption is because the data isn't in a schema. The organisation I work for are very new to Power BI and business intelligence, so everything at the moment is all exploratory.

Solution

  • Managed to fix the issue. After removing the relationship with Stage1data and Stage2data the figures came out correct.