ISINSCOPE Dax command help

[KN_HEALT47]

I wrote this measure with the help of a forum user: 

YTD Budget =

IF(OR(ISINSCOPE(Expenses[partner_name]),ISINSCOPE(Expenses[VendorJA])), BLANK(),CALCULATE(SUM(Budget[Amount]), MONTH(Budget[Merged Month Year]) <= MONTH(TODAY())-1))

and the result is this:

I like that the invoice detail is blank for the parts of the budget that do not have this detail the problem is that the YTD budget is aggregating on repeat for each account line ( see 50001 Payroll). Idealy the total budget YTD would populate for 50001 payroll only and so on for each account and 493,604.01 would only be the total at the bottom. Both budget and Actuals share the account detail, only budget does not go further down into invoice and vendor name. Let me know if there is a way to modify the dax to include this! Thanks in advance.

[daxer-almighty]

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.

[KN_HEALT47]

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.

[daxer-almighty]

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.

[KN_HEALT47]

Looks promising thanks for your time and attention!

[daxer-almighty]

@KN_HEALT47 

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.

[daxer-almighty]

Hard to write DAX without knowing the model...

[KN_HEALT47]

Is this it?

[daxer-almighty · Accepted solution]

If you need to see how budgeting should be handled, you can go to this site: Budget – DAX Patterns

[KN_HEALT47]

Sorry, I am not sure what the "model" is