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
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.