Relationship manager error, maybe?

[rpul]

I am looking for any idea as to what might be driving the following issue:

I have the following graph and associated table

Both the graph and table render correctly except for when I apply a specific filter and then only for 2010. As can be seen in the graph, the red and dark blue bars are very high for 2010. This doesn't seem to me to be possible since (1) the red and dark blue bars are a subset of the data making up the light blue bar and (2) the back-end relationship is such that the light-blue comes from one table, which is what is filtered by the slicer and from there is matched to a second table that creates the red and dark blue bars. The 'see records' option is also not available for some reason, even if I remove the measures on the graph or switch the graph around. These two tables are technically connected via a third table for one to one connections and through a date table.

If this issue weren't weird enough, then looking at the table, the second, fourth, and sixth columns are clearly being summed incorrectly. The second and fourth columns correspond to the red and dark blue bars.

When looking at the graph, I can use the drill down feature to actually isolate to the day that seems to be throwing things off. What's even stranger though is that the degree to which the day in question is off seems to get multiplied as you move up in the heirarchy. As you can see below, the 19th is off by about 15 units, but when looking at the year this seems to get multiplied to the thousands. Does anyone have a guess as to what might be going on? I've checked the data in R and everything seems normal there. I've also filtered for the date in the query editor and it only returns 4 units.

[stretcharm]

@AlbertoFerrari has a greate video that explains dax optimisation and tracing

You could try looking at the traces of the DAX to see if you can spot the problem.

https://www.sqlbi.com/tv/dax-optimization-examples/

This is shorter video of dax tracing

https://www.sqlbi.com/tv/dax-studio-2-7-improved-debug-experience/

This uses DAX studio to do the tracing

https://www.sqlbi.com/tools/dax-studio/

If this doesn't help you will need to provde an sample to help us understand your data and expressions.

[rpul · Accepted solution]

Thanks @stretcharm for the suggestion. After looking through the DAX query I wasn't able to locate the error there. I was able though to eventually find where the error was coming from, but I can't explain why it is happening. The error was because I had duplicate values for my primary key with different dates attached to those rows. I found the error through a lot of experimentation and realizing that when I used the Date column from my original table rather than from my Calendar generated table that the error went away. The strange part was that I previously had removed the duplicates in the query editor, but after I had filtered by year. To give a better sense of how the problematic data looked:

ID              Date

1234          1/1/2009

1234          11/19/2010

What I was doing in the query editor was (1) removing duplicates, which seemed to drop the 2009 entry based on what I saw in the 'see records' of other tables I built and kept the 2010 entry. Then (2) I filtered for dates after 2009. But when I flipped this so that I filtered for things after 2009 first and then removed duplicates the error I described disappeared. I'm not sure why the first query editor sequence would later cause such an error. For anyone else that may experience this, here is my data relationship diagram: