Forum Discussion

arunbyc's avatar
arunbyc
Icon for Helper III rankHelper III
1 year ago
Solved

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_Quant...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

arunbyc's avatar
arunbyc
Icon for Helper III rankHelper III
1 year ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • arunbyc's avatar
      arunbyc
      Icon for Helper III rankHelper III
      1 year ago

      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