Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Issue with table relationships

I created a measure in fact table which uses one field of  table 'd_prd_targets' but this measure is giving same total value for any filtered field in fact table. Why there is an issue in data filter between these two tables?

 

 

 

tamerj1 

Need Help
Show and Tell
Tips and Tricks
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    Your measure is based on a column that belongs to a dim. Table which is 'd_prd_target'. It cannot be filtered by Fact and cannot be filtered by other dim. It is strange that you are slicing by a field from a fact table. Whatever your reason is you can do the following:
    Let's say your calculation is SUM ( 'd_prd_target'[Value] ) then in order for this measure to be filtered by the fact table you need to write it

     

     

    CALCULATE ( 
    SUM ( 'd_prd_target'[Value] ),
    CROSSFILTER ( 'FactTable'[Product Key] , 'd_prd_target'[Product Key], BOTH )
)

     

     

     

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 

    your model is totally fine. However let me try to answer your question. 
    1. It doesn't matter in which table a measure is stored in. It will behave completely the same. It is just stored in that table but it doesn't actually belong to it. Measures belong to the model the same way tables themselves belong to the model. 
    2. in lieu of obove a measure is evaluated based on the filter context only regardless of which table it is stored in. 
    3. The measure that you have described in your question is based on a dim. Table. And the filter context is apparently based on other dim. Table(s). In a stat schema, dim. Tables do not filter each other, they only filter the fact Table. Therefore, when a measure is based a dim. Table, extra dax need to be written in order to trasfer the filter from one dim. Table to another. 
    4. In order to solve your problem you need to provide more context and more of the issue as power bi is all about details and filter context. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks tamerj1, very helpful as always.

       

      The newly created measure is not getting filtered even based on a field in fact table. In this measure I have just done a simple sum of one field in 'd_prd_target' table. I'm attaching few screenshots for reference.

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Anonymous 
        Your measure is based on a column that belongs to a dim. Table which is 'd_prd_target'. It cannot be filtered by Fact and cannot be filtered by other dim. It is strange that you are slicing by a field from a fact table. Whatever your reason is you can do the following:
        Let's say your calculation is SUM ( 'd_prd_target'[Value] ) then in order for this measure to be filtered by the fact table you need to write it

         

         

        CALCULATE ( 
    SUM ( 'd_prd_target'[Value] ),
    CROSSFILTER ( 'FactTable'[Product Key] , 'd_prd_target'[Product Key], BOTH )
)