Issues with filtering when a specific measure is applied

Question

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,

anonymous · Accepted Answer

Hi Anonymous&nbsp;,
&nbsp;
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.
&nbsp;
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
    )
)

&nbsp;
You can change 'SelectedDepartment' to whatever data you have in the Filter, in this case 'A' 'B' 'C'.
&nbsp;
I know it is a bit of a pain in the ass but it is an alternative.
&nbsp;
The result is ideal.

&nbsp;
If you have any other questions please feel free to contact me.
&nbsp;
The pbix file is attached.
&nbsp;
Best Regards,YangCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;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.&nbsp;Thanks a lot!

Forum Discussion

Issues with filtering when a specific measure is applied

2 Replies

Related content

Make measure ignore specific filter

How to search specific word in table filter Calculations

Dynamic measure filtering

Specific Filter

Filter by Rank measure