Aggregation functionality returns wrong total

[Anonymous]

I have a fact table (direct query), fact table aggr (import) and a date table (dual). 

When I pull in the year from the Date and pull in a non-aggr value, I get a result where the lines are correct but the grand total is not:

Why does this happen? When I take that year (Date table) field out, it shows that grand total correctly:

And it hits the aggr fine- so the functionality works until I pull in the date dimension field. 

Any thoughts?

Thanks.

[Anonymous]

@Mikelytics  thanks for pointing that out. Staring at problem too long I guess :). that resolved it.

This was actually something I was doing to try to figure out a different problem. I get that same wrong grand total if the date table is something generated as a manual table/procedure within the pbix file's transformation.  It seems that because a manual table is always IMPORT (and can't be DUAL), that the aggregation functionality won't work with it. I will need to create that date dimension as a table in my source database so I can use it as DUAL type in the model.  Thanks!

[Mikelytics · Accepted solution]

Hi @Anonymous 

Did you try to change the many:many relationship to a one:many relationship?

Best regards

Mikelytics

Did I solve your request? Please mark my post as solution. 

Appreciate your Kudos.

[Anonymous]

Yup- that was my issue for the specific setup I noted in the original posting.

Thanks!