Measure to lookup a value from another table breaks visual

[ezaidi]

Hi, I have a bit of a strange problem. I have 3 tables. SalesOrders, SalesOrderLines and ProductionsOrders. SalesOrders is linked to SalesOrderLines with a key in a one-to-many relationship. The ProductionOrders table is not linked to any of the two tables.

I am trying to create a table visual that shows some data from salesorderlines (mainly) and a few attributes from SalesOrders table. I also created a measure in the salesOrderLines table that gets the maximum value of a production order from the productionorder table based on an id (effectivityUnit) that exists in both salesorderlines table and ProductionOrders table.

The problem is that when I create my visual using ONLY fields from the salesorderlines table, my measure works and everything displays fine. As soon as I drag in a column from the salesOrders table, it breaks the visual. I would have thought that because these tables are linked, that this would work fine.

My measure is as follows:

!productionOrder = CALCULATE(

                    MAX(ProductionOrders[productionOrder]),

                    FILTER(ProductionOrders,

                    ProductionOrders[effectivityUnit] = SELECTEDVALUE(SalesOrderLines[effectivityUnit])              

                    ))

I will attach a sample pbix file to better explain the issue.

[sergej_og]

What would happen when you connect Productionorders also with other table?
As I can see there is a matching key for that. 1-m or n-m relationship?
Hard to undestand with given informations.

Regards

[ezaidi]

ok glad you asked, if I make a relation between salesOrderLines and ProductionOrders table using the effectivityUnit field, then the relationship is many to many. Keep in mind, I would be connecting to a dataset directly, so I wouldnt even be able to make that relationship on the main published data set if I wanted to. I just need a way to create a measure that will get the max or one production order based on effectivityUnit in the salesOrderLines table. I cannot use any calculated columns since I will be using a directly connected dataset. Can this be done?

[ezaidi]

I cant seem to attatch my pbix here. Here are some screenshots of what it looks like:

1) This works fine. Most of the columns here are from SalesOrderLines table and one column at the end from SalesOrders

2) This works fine. Most of the columns here are from SalesOrderLines table and one column at the end is the measure mentioned above getting max value from ProductionOrders table.

3) Now i want to have the same table with both the Rev00Date and the productionOrder measure column in the same visual and it breaks with a visual memory exceed error.