Forum Discussion
why does measure affect filter context in matrixI
Hi arunbyc ,
I think your issue should be caused by the relationship between Order Table and Work Table.
Here I create a sample:
In my sample Order[Order_Id] connect to Work[Order_Id] (one to many)
Result is similar like yours:
You can update your measure as below.
Rate = CALCULATE(DIVIDE(sum('Order'[Est_Amount]),sum('Order'[Est_Quantity])),CROSSFILTER('Order'[Order_Id],'Work'[Order_id],Both))I am confused to your data model. I think Work Table should be a DimTable for Order Table like Project Table.
Can an order id correspond to more than one work id and name, otherwise I think the relationship should be one to one.
If relationship looks like as below, your measure works.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi arunbyc ,
I think your issue should be caused by the relationship between Order Table and Work Table.
Here I create a sample:
In my sample Order[Order_Id] connect to Work[Order_Id] (one to many)
Result is similar like yours:
You can update your measure as below.
Rate = CALCULATE(DIVIDE(sum('Order'[Est_Amount]),sum('Order'[Est_Quantity])),CROSSFILTER('Order'[Order_Id],'Work'[Order_id],Both))
I am confused to your data model. I think Work Table should be a DimTable for Order Table like Project Table.
Can an order id correspond to more than one work id and name, otherwise I think the relationship should be one to one.
If relationship looks like as below, your measure works.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Your reply made me see what is going on. Thank you.. I overlooked the fact that unlike the fields [quantity] and [amount] which caused no issues though they belonged to the orders table, the division is a measure without row context. So I thought of mimicking the row context by using
HasOneValue(work[work_name]), and it worked.
I am still learnring Dax, so do not know if my use of HasoneValue as a substitute for row context always works or not. I would appreciate if your comments, not about just the code, but my interpretation as to why Qunatity and AMount fields did not pose the same problem the measure did, and also the use of hasonevalue() in this context.
Arun