Measures and Slicers Interaction

[---Andrea---]

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--- · Accepted solution]

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!

[lbendlin]

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---]

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 🙂