USERELATIONSHIP and many to many

[Velafyko]

Hi all

In my data model I have an "Order" table and an "Invoice" table. They are linked by the column "Order number". Since there are allready a lot of relationships in the model, I just have an inactive relationship between "Order" and "Invoice". This is a many-to-many relationship.

Order

Order number

Amount

Invoice

Invoice number

Amount

Order number

Now I want to compare an invoice with how much is ordered. I create a visulization table with invoice number and amount to get a list of all invoices. Then I try with a DAX statement to get the related order sums:

RelatedOrderSum= CALCULATE(SUM(Order[Amount]), USERELATIONSHIP(Invoice[Order number], Order[Order number]))

This works when there is only one order with the corresponding order number, but it fails if there are two orders. Then it only returns BLANK, and not the sum of them. How can I fix this to make it work with any number of orders?

[mahoneypat]

I agree you should improve your model and get rid of the M2M, but FYI there is another way to path the Order Number filter between the tables.

RelatedOrderSum= CALCULATE(SUM(Order[Amount]), TREATAS(VALUES(Invoice[Order number]), Order[Order number]))

You can swap the two order number columns in the TREATAS; not sure which invoice # column you are using in the visual.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Velafyko]

Unfortunately I don't get your formula to work. It just gives me the same result as my original formula with the USERELATIONSHIP function. It works when there is only one order, but not when there are multiple orders for an invoice.

[Anonymous]

Hm... Interesting. You're saying that one order can be found on different invoices and that one invoice can have different order numbers? As much as I understand the latter, I'm a bit surprised at the former.

Many2Many is useful only for scenarios where there's a granularity problem. Nothing else. You don't have a granularity issue here, so I'd change the design to remove Many2Many, since it's not only slower but can also be very hard to work with if you don't know all the nuances of such a design.

[Velafyko]

Yes, one order can lead to multiple invoices. For instance when not everything ordered is in stock, then what is in stock is shipped together with an invocice. When the remaining items are back in stock, they are shipped with a new invoice from the same order. Ideally we should have exactly one invoice for each order, but it's not always the case.

I hoped I could avoid changing the data model, but it seems like I have no choice then. Thank you 🙂

[amitchandak · Accepted solution]

@Velafyko , create a common dimension of Order number, refer the code in the file

https://www.dropbox.com/s/op9lb78w9utdonz/Distinct%20from%20two%20Tables.pbix?dl=0

refer

https://www.seerinteractive.com/blog/join-many-many-power-bi/

[Greg_Deckler]

@Velafyko - I would avoid many-to-many relationships like the plague and use a bridge table of unique order numbers.