Forum Discussion

---Andrea---'s avatar
---Andrea---
Frequent Visitor
1 year ago
Solved

Measures and Slicers Interaction

Good afternoon,

I would like to ask for help and understanding on the relationship between slicers and measures.

I have two tables, AE and DM, linked by a 1-to-many (DM is 1 and AE is many), bidirectional relationship on a column called URCFID.

 

I also have the following calculated column in DM: 

Incidence Rate = 
    COALESCE(
        DIVIDE(COUNTROWS(RELATEDTABLE(AE)), [Duration (months)]),
        0
    )
 
Ultimately, what I am interested in is the average of this Incidence Rate column when some filters are applied.
 
In this setup, the following happens:
1) When no slicer filters are applied, the calculation yields the expected result
2) When I apply a slicer filter on DM, the calculation also yields the expected result (AE is filtered, but DM still has the UCRFIDs not present in AE, which yields a 0 for that row and contributes to the average calculation).
3) When I apply a slicer filter on AE, the result is wrong: DM is also filtered to keep only the UCRFID present in both AE and DM. This is undesirable and is the behaviour I want to eliminate.
 
Of course I tried changing the relationship's cross-filter direction to be single (DM to AE), but these are the results:
1) (Like above) when no slicer filters are applied, the calculation yields the expected result
2) (Like above) when I apply a slicer filter on DM, the calculation also yields the expected result
3) When I apply a slicer filter on AE, visualizations that only rely on AE show the correct change (AE is filtered), but the measure's result does not change, as if no filtering was done to neither DM nor AE.
 
Any help in understanding what is going on with the measure ignoring filters on AE would be greatly appreciated.
Thank you in advance ğŸ™‚
  • ---Andrea---'s avatar
    ---Andrea---
    1 year ago

    For anyone facing the same problem, here is the solution I found: use a measure instead of a calculated column.

     

    Since I was ultimately only interested in the average of the column, I created a new measure as such:

    Average Incidence Rate = 
    AVERAGEX(
        DM, 
        COALESCE(
            DIVIDE(COUNTROWS(RELATEDTABLE(AE)), [Duration (months)]), 
            0
        )
    )

     

    My best guess (which I haven't been able to find a reference for in the documentation) is that calculated columns (and calculated tables) are probably pre-calculated before any visual filters are applied to the model. Therefore what I was getting earlier was that 

    COUNTROWS(RELATED(AE))

    was happening before the AE table was filtered by the visual, and the results therefore stayed constant.

     

    Measures, on the other hand, are likely calculated as the last step in the process, and hence happen after the tables are filtered by the visual filters.

     

    I hope this helps someone else. Good luck!

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    The standard setup is a Star Schema with a Dimension Table controlling a Fact table in a single direction.  Your setup varies substantially from that.

     

    A measure is recalculated if the user interacts with a slicer, the measure depends on that value, and the measure is included in a visual.

     

     

    • ---Andrea---'s avatar
      ---Andrea---
      Frequent Visitor
      1 year ago

      Good morning!

      I don't understand why you say my setup varies from that. I have a star schema with DM at the center. DM is related to AE via a uni-directional filter.

      When using a slicer, the measure gets updated. Just not in the right way.

      I am not sure that I understand your answer.

      Any clarification would be helpful to learn more 🙂

  • ---Andrea---'s avatar
    ---Andrea---
    Frequent Visitor
    1 year ago

    For anyone facing the same problem, here is the solution I found: use a measure instead of a calculated column.

     

    Since I was ultimately only interested in the average of the column, I created a new measure as such:

    Average Incidence Rate = 
    AVERAGEX(
        DM, 
        COALESCE(
            DIVIDE(COUNTROWS(RELATEDTABLE(AE)), [Duration (months)]), 
            0
        )
    )

     

    My best guess (which I haven't been able to find a reference for in the documentation) is that calculated columns (and calculated tables) are probably pre-calculated before any visual filters are applied to the model. Therefore what I was getting earlier was that 

    COUNTROWS(RELATED(AE))

    was happening before the AE table was filtered by the visual, and the results therefore stayed constant.

     

    Measures, on the other hand, are likely calculated as the last step in the process, and hence happen after the tables are filtered by the visual filters.

     

    I hope this helps someone else. Good luck!