Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
dpombal
Post Patron
Post Patron

DAX REMOVEFILTERS failing on a measure

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.

 

blogCom1.PNG

 

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.

blogCom2.PNG

 

thanks in advance

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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.

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

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.

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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.