Issues with filtering when a specific measure is applied

[dwade]

I have a specific data structure which I can share here, basically the data consists of individual reviews and aggregate reviews by specific companies and the various subordinates within each company. A company can belong to one or many Departments which in turn can belong to one or many Organisations which are mapped using a reference table called 'Account'.

I have the following table, just a very simple one:

And I can filter it by "Department" which certain Companies belong to:

OK great, that's fine.

But when I add this measure:

Month vs Average = IF(ISBLANK([Average Rating Past Month]), "No reviews past month",Review[Average Rating Past Month] - OverallRating[Overall Average Rating])

It works as expected without filters:

But it breaks the same filter as I was using earlier:

I understand this has something to do with filter context and seems to be triggered by using either IF ISBLANK, or IF <= 0 and such which I sort of see why. But after spending a couple of days trying to work around, I'm at a loss.

Can anyone shed some light on the best practices for approaching this problem and getting around it?

Thanks,

[v-huijiey-msft · Accepted solution]

Hi @dwade ,

As you correctly point out, this issue is related to the filter context. When filters are applied, they change the context in which the metric is calculated.

Please try:

MonthVSAverage = 
VAR SelectedDepartment = SELECTEDVALUE('Department'[DepartmentName])
RETURN
IF(
    SelectedDepartment = "A",
    CALCULATE(
        IF(
            ISBLANK(MAX('Table'[Average Rating Past Month])), 
            "No reviews past month",
            MAX('Table'[Average Rating Past Month]) - MAX('Table'[Overall Average Rating])
        ),
        'Department'[DepartmentName] = SelectedDepartment
    )
)

You can change 'SelectedDepartment' to whatever data you have in the Filter, in this case 'A' 'B' 'C'.

I know it is a bit of a pain in the ass but it is an alternative.

The result is ideal.

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[dwade]

Hi @v-huijiey-msft ,

Yes that worked! It is unfortunate that the SelectedDepartment needs to be predefined because that makes things tricky but I can probably work around it.

Thanks for your help 🙂