Forum Discussion
why does measure affect filter context in matrixI
I have three tables Project -----> Order ----- > Work ---> (This gets linked to other tables later)
Structure of the tables:
Project : Project_Id, name
Order : Project_Id, Order_Id, Est_Quantity, Est_Amount
Work: Order_id, Work_Id, Name
I created a measure
When I did not insert the measure in the matrix, the matrix displayed the rows as shown the Fig-1, as I expected, but when I included the measure as a value column in the matrix, the matrix seemed to ignore the earlier filter context, and displayed the rows as in Fig-2. I am trying to understand what causes this.
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.
7 Replies
Hello arunbyc ,
This issue could be because of the Wrong model setup, Kindlys share us the Model view to check on the further..also check if the One-To-many relationship has been applied corecctly between tables.
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
I posted my model. forgot if I sent it as a reply to you or not.
Hi arunbyc
The the Work table can't be used in this scenario. Although it has a relationship with the Order table, the Order table only filters the Work table not the other way round. So when adding the column from the work table, this is the expected result.
What are you trying to acheive? If you want to show just the Work done, then add an extra table, when someone clicks on an Order in the first visual, this will filter the new table with the work.
Let me know what you want to acheive and I can take a look for youJoe
No I am not adding a column from the work table. I posted my model in the same thread in reply to dharmendars007. Can you pls see and let me know what you think?
This is the model. This is a simplified version I tried at home to reproduce the error. (I know I do not really need the rate to be in a measure, can get rid of it but I am new to PowerBI and wanted to learn *why* this happens). I can send the pbix file if you think that could help.
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.
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
