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
Hardyyyp
New Member

RemoveFilter Function Not Working Within Calculate when Last.Date is used as a Filter

Hi All, 
 
I've simplified my Data/Measure and attached a screenshot.
 
The Formula within my Measure is as follows:
  
CountRows for LastDate = CALCULATE(COUNTROWS(Sheet1),LASTDATE(Sheet1[Date]),REMOVEFILTERS(Sheet1))
 
I want to count rows only for the last date within the date column. Additionally, Ignore any Filters that are applied within the visuals. 
 
The desired value here should be 4. 
 
This works for all scenarios in the screenshots attached. 
 
Except when "Pre 10" or "01 January 2023" is Selected within the slicers. 
 
The Measure should Ignore All filters within the table. 
 
I think this is due to the fact that neither "2 Jan 2023" nor "Pre 10" have any values for the last date in the data.
 
Which is wrong, either there's an error within PowerBI or  I'm not understanding how these functions worked, (Most likely the latter). 
 
Would someone be able to suggest an alternative to achieve this?
 
 
 
 Model View .jpgData Pic.jpgNo Filters.jpgNorthampton Selected .jpg

  

PM Selected .jpgHardyyyp_0-1692290489868.png

 

  

2 REPLIES 2
Hardyyyp
New Member

Hi Amitchandak, do you know why my formula doesn't work, is this because of a PowerBI error? 

amitchandak
Super User
Super User

@Hardyyyp , Try like

 

CountRows for LastDate =
Var _max = maxx(allselected(sheet1), Sheet1[Date])
return
CALCULATE(COUNTROWS(Sheet1),filter(Sheet1, Sheet1[Date]= _max))

 

OR

 

CountRows for LastDate =
Var _max = maxx(all(sheet1), Sheet1[Date])
return
CALCULATE(COUNTROWS(Sheet1),Sheet1[Date]= _max)

 

 

refer if needed

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

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.