DAX REMOVEFILTERS failing on a measure

[dpombal]

Hi all i am using may update version  and a formula is failing.

My problem is very easy to explain I want to fix a calculation to get the average of distance FOR ALL DATES ( so I need to remove filter on this DATE field, but if I add to the page a slicer, the measure changes. I want to remove explicitly filters.

Here is the sample PBIX to test what I mention https://1drv.ms/u/s!Am7buNMZi-gwg8hT4eo1bkHP975Egg?e=goyfU7 

My problem is REMOVEFILTERS  is not working fo TableGPS[DATE] column and also not working for TableGPS[DRILL]

In the below formula REMOVEFILTERS is not working, I also tried with ALL(TableGPS[DATE],TableGPS[DRILL])

Formula

DISTANCE TOTAL AVERAGE MD v1 = CALCULATE(

     AVERAGE(TableGPS[DISTANCE]),

    TableGPS[Type]="MD",

    TableGPS[DURATION]>=70,

   REMOVEFILTERS(TableGPS[DATE],TableGPS[DRILL])

)

Image1 - If I don't filter any date I get the value correct.

In this table we get the correct value of 9940.07 for the measure.

Image2- However after filter 21/03/2023 the value CHANGES and a I want to get the value 9940, my measure needs to avoid the filter by DATE column and also by DRILL Column.

thanks in advance

[johnt75 · Accepted solution]

The problem is because you just have one table rather than a star schema. SUMMARIZECOLUMNS, which is used by the engine to generate the data for all your visuals, can do funny things when all the filters are being applied to the same table.

In the attached file I have created dimension tables for both date and drill and created the [DISTANCE TOTAL AVERAGE MD v3] measure based on your original code but removing the filters from the dimension tables instead of the fact table.

[dpombal]

Hi John thanks for your help, I promise you this code worked on November 2022 version of the Report,  looks strange why this worked and fails now.

Adding extra tables and modeling a star is correct but It will take me more time.

Thanks!