how to stop page filters from affecting values in other filters for a measure

[bsheffer]

I have a page where I want to filter a value on 3 fields but I also want to show that value without one of the filters (the retention date filter)

All these filters may be modified by the user

I am counting the number of merchants

with all these filters merchant # = 50

with the retention date filter removed the number is 11,222

I've tried to create a measure for the value without the retention date filter but it returns the value of 10,411.  I'm using values() to get the valid values in the first 2 filters so I'm thinking that the retention date filter is affecting those values before I can exclude it in the measure.

this version of the measure returns the 10,411 count

_count_mid_locations_ignore_date =

var _closed = values('Dim View_MID_Properties'[DATE_CLOSED])

var _tier3 = values('Dim View_MID_Properties'[Hierarchy2019_TIER3])

return

CALCULATE(count('Dim View_MID_Properties'[MERCHANT_NUMBER])

,all()

,_closed

,_tier3

)

however if I explicitly use the currently selected filter values it does work (11,222)

_count_mid_locations_ignore_date =

CALCULATE(count('Dim View_MID_Properties'[MERCHANT_NUMBER])

,all()

,not 'Dim View_MID_Properties'[Hierarchy2019_TIER3] in {"FI - Agent", "ISO"}

,'Dim View_MID_Properties'[DATE_CLOSED] = BLANK()

)

this won't work in practice as I want the measure to reflect the settings of the date_closed and tier3 page filters.

I suspect that the Retention Date page filter is affecting what is in the values statements before it gets to the all() statement

I've tried removefilters and allexcept to remove all the filters except for date_closed and tier3 but I always get the 10,411 result as long as the Retention date page filter is set.

Am I missing something?  Please give me a better version of 

_count_mid_locations_ignore_date =

var _closed = values('Dim View_MID_Properties'[DATE_CLOSED])

var _tier3 = values('Dim View_MID_Properties'[Hierarchy2019_TIER3])

return

CALCULATE(count('Dim View_MID_Properties'[MERCHANT_NUMBER])

,all()

,_closed

,_tier3

--,not 'Dim View_MID_Properties'[Hierarchy2019_TIER3] in {"FI - Agent", "ISO"}

--,'Dim View_MID_Properties'[DATE_CLOSED] = BLANK()

)

[bsheffer · Accepted solution]

what finally worked was moving the fields in the page filters to dim tables further out the snowflake.

closedDate already existed

tier3 went to dim tier3

retention date went to dim retention date.

that way the relationship direction to the main table kept the other filters from changing the values available to the filters on the dim tables.  Tier3 was no longer being prefiltered by retention date.

now when I pulled the values in the function variables, they got all the values available to the dim table, and when applied after the all() statement, filtered properly.

Removefilter probably would have worked also but only after I moved the filters to the dim tables.

[lbendlin]

Create a measure that includes REMOVEFILTERS() on the field you want to exclude from the filtering.

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523