Sum and substraction between 3 tables linked by another table.

[GotThePowerBI]

Hello people,

I have the following issue. I have 3 tables that contain finnancial data 'Production', 'InvoicedAmounts' and 'Manual update'. The data they contain is managed by the 'BusinessLine' dimension table to which all of them are linked.

I just need to perform a simple operation substracting one amount from table Production to table Invoced amounts and add the Manual Update, for all rows.

I have been trying to create a calculated column from one of the three tables containing the amounts to no avail.

I wanted to establish a bidirectional filtering between the thress of them and the 'BusinessLine' but since two of them are linked to the calendar, Power BI doesn't like it.

Here is the schema

Thank you for your help.

[Anonymous]

Are you sure your model is not ambiguous? Please read about ambiguous models before you shoot yourself in the foot...

Best
Darek

[Stachu]

you can try with TREATAS
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[GotThePowerBI]

Thank you for your suggestion Stachu, this is the formula I'm trying to apply but it doesn't work:

Amount =
CALCULATE (
SUM ( PRODUCTION[Figures] ),
TREATAS ( { InvoicedAmount[Figures] }, 'Manual update'[Figures] )
)

This is what  I want to achieve: PRODUCTION[Figures] - InvoicedAmount[Figures] + 'Manual update'[Figures] = Amount

[Anonymous]

Mate, I don't want to frighten you but your model is *ambiguous*. The reason is there are at least 2 different paths from Calendar to Production. Such a model will at one point in time give you incorrect numbers and you will not even be aware of this... You've been warned.

Best
Darek

[GotThePowerBI]

Thanks for the warning darlove. I'm really starting and I read about ambiguous models. What is your suggestion? My problem is that the three tables 'production' 'invoicedamounts' and 'manual update' contain dates that I need to exploit. Would a single relationship from one of them to the calendar suffice?

[Anonymous]

I can't tell you much apart from the fact that you might need to change the model. I've taken a course on data modeling on sqlbi.com and thus I know what problems one might face and how to deal with them. I'm not saying you should shell out as well but one thing is for sure: without a good understanding of data modeling you'll be very often trying to squeeze a square peg into a round hole...

Your model is ambiguous because you've got cross-filtering enabled on one of the relationships (well, at least from what I can see in the pic you pasted). The advice is to use cross-filtering only when strictly needed and enable it in a measure through the USERELATIONSHIP directive to CALCULATE.

Best
Darek

[GotThePowerBI]

"Your model is ambiguous because you've got cross-filtering enabled on one of the relationships (well, at least from what I can see in the pic you pasted). The advice is to use cross-filtering only when strictly needed and enable it in a measure through the USERELATIONSHIP directive to CALCULATE."

That cross-filtering allowed me to get the right amounts for the invoicedamounts table, as this one filters on another table where all the invoices are stored.  When I disable the cross-filtering the amounts are wrong.

So, it's strictly needed.

The paradox of my problem is that now, I see all my data and values for all combinations of dimensions BUT I cannot get the right results when trying to do simple arithmetic operations between them.

[Anonymous]

Cross-filtering can be enabled on demand in a measure. My advice to you is to re-think the model. Otherwise, you might be in deep trouble very soon (if not yet).

By the way, what's the use of the model that is ambiguous and you cannot predict when and whether it calculates a correct number or not? You should really watch/read some material about proper data modeling. Sorry. I can't help you any more than that.

Best
Darek

[Stachu]

here is an excellent video on the topic:
https://sqlbits.com/Sessions/Event18/Understanding_relationships_in_Power_BI

the rule of thumb - I would avoid bi-directional relationships