Forum Discussion
ISINSCOPE Dax command help
If you need to see how budgeting should be handled, you can go to this site: Budget – DAX Patterns
Yes, that's the model.
But wait... Many to many? I have serious doubts about the legitimacy of this model. Why are you using the many to many relationship? And why can't I see the filtering direction on the path? On which fields do you join the tables? If you don't have a granularity issue here, then this model is wrong and should be dropped.
Many to many relationships are not something one should take lightly in PBI. They are dangerous and can lead to formulas returning seemingly random numbers. There are only certain, very precisely defined circumstances in which such relationships should be used. The correct situation for employing these relationships is a granularity issue. ONLY. And even then the filtering direction must be one-way only.
I got a feeling that powerbi did not like many to many. To be fair I am new and self taught through trial and error in powerbi so I haven't the slightest clue what makes a model legitamate or not. This means that many things I do/have done are not othodox or by the book I am sure but I am just trying to make it work. Both data sets share common columns and the relationship manager would not let me pick different cardinalities. I think the issue is one is in excel and one is a direct query I wrote. Unfortunately I do not possess the knowledge to in SQL to join the two given the granularity difference. Given what you have said is it still possible to have the budget (granularity ends at account) and the actuals or odoo financials (ganularity goes all the way to the invoice level) on the same visual but have budget be blank when you drill down to levels of granularity it does not posses? Furthermore have it populate subtotals at the levels the data shares common detail IE account level?
Thanks.
I understand your model is composite (you always have to state it as it's important). That changes the perspective a bit. I remember that between different islands or continents (these are technical terms) the only possible relationship is many-to-many. But even then you should change the filtering to be one-way only. The filter direction is from the table with a lower granularity to the one with a higher one. Your example does guarantee this since one table is on the account level granularity and the second one on the invoice level. The former table should filter the latter.
To answer your questions: yes, it's perfectly possible to have the setup you're talking about. I think the pattern I gave you a link to covers this.
Looks promising thanks for your time and attention!
Be careful, though. I'd encourage you to read this: Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Docs
You really need to fully understand the ramifications of what the document describes to know how to design/refine your model in order to always get correct figures.