measure is calculating but ignoring page filter.

[MarkD1722]

I have the measure below (one of 12 monthly values).  Prior to this current version you see, I had no last filter line regarding "APPROVED" amounts; I had only the  "<> 'Amount Delta' " filter criteria.  Rather I had a page filter set to "Category is not equal Approved ."  However, the measure was including the values where the Category was equal to "Approved".  We know this because these Approved amounts were added as recently as November, and only those values of Nov and Dec have been affected.  When I added the filter to the measure, it calculated correctly.   I don't have any explicit filter killers (e.g., REMOVEFILTERS or ALL) in the measure, so I am unsure why the Approved amounts were included.   Any idea why this DAX was ignoring the page filter for Approved values?  The issue must be the same for the "Amount Delta," but that was there from this measure's earliest version and I don't recall why I needed it exactly.  (Amount Delta is also filtered out at the page level with APPROVED.)

November Amount =

VAR Select_Curr = SELECTEDVALUE(Currency_Exchange_Rate[Currency])

VAR Amt_Yr_Mon = MAX('Final Amount'[Amount Year_Month])

VAR Resulting_Exch_Rate = CALCULATE(MAX( 'currency_exchange_rate_table'[Exchange_Rate])

                                    , 'currency_exchange_rate_table'[Currency] = Select_Curr

                                    , 'currency_exchange_rate_table'[Currency Year_Month] = Amt_Yr_Mon)

VAR SelectYear = SELECTEDVALUE('Year List'[Year])

RETURN

Calculate(SUMX('Final Amounts','Final Amounts'[Amount USD])

, (DATESBETWEEN('Calendar Table 2'[DATE].[Date]

, DATE(SelectYear,11,1),DATE(SelectYear,11,30)))

, ('Final Amounts'[CATEGORY]<>"Amount Delta")

, ('Final Amounts'[CATEGORY]<>"APPROVED"))

* Resulting_Exch_Rate

[Anonymous · Accepted solution]

Hi @MarkD1722 ,

Here are some possible causes and solutions:

1. Please check the relationships in the data model. If there are any inactive relationships or if the relationships are set up incorrectly, the filters may not work as expected.

2. If your measures depend on other measures or calculated columns, make sure that these dependencies also honor the page-level filters. Verify if there are other filters or slicers that may override the page-level filters.

If these do not resolve your problem, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[MarkD1722]

Thank you for the suggestions.  I will examine the "build" as it has relationships and "layers" which lend complexity.  After reviewing, should I find anything, I will return with my results.  Thanks.

[MarkD1722]

I have 12 measures (i.e., monthly calculations) each of which has the code below.  As of November, we added amounts categorized as "APPROVED" and those amounts would normally be included in the sums.  However, I don't want them included, so I added a page level filter (i.e., CATEGORY is not "APPROVED").  The measures, however, continue to include them.  So, I had to add a filter line within the DAX to filter them out specifically...  but I don't know why the page filter doesn't work for this like it does for other calculations going on.  I would expect that a REMOVEFILTERS or ALL function would cause that, but I don't have any in this measure or related measures.  Although I cannot recall why there is also a dedicate line for "Amount Delta," it must be that I had this issue before... because Amount Delta is also filtered out at the page level.

November Amount =

VAR Select_Curr = SELECTEDVALUE(Currency_Exchange_Rate[Currency])

VAR Amt_Yr_Mon = MAX('Final Amount'[Amount Year_Month])

VAR Resulting_Exch_Rate = CALCULATE(MAX( 'currency_exchange_rate_table'[Exchange_Rate])

                                    , 'currency_exchange_rate_table'[Currency] = Select_Curr

                                    , 'currency_exchange_rate_table[Currency Year_Month] = Amt_Yr_Mon)

VAR SelectYear = SELECTEDVALUE('Year List'[Year])

RETURN

Calculate(SUMX('Final Amount','Final Amount'[Amount USD])

, (DATESBETWEEN('Calendar Table 2'[DATE].[Date]

, DATE(SelectYear,11,1),DATE(SelectYear,11,30)))

, ('Final Amount'[CATEGORY]<>"Amount Delta")

, ('Final Amount'[CATEGORY]<>"APPROVED"))

* Resulting_Exch_Rate