Forum Discussion
Page-Level Filtering based on the Latest Date
Dear Developers,
I'm a newbie to PBI, my requirement is "I want my Power BI Report/Dashboard visuals should show the "Latest Data" in every VISUALS on every refresh. I tried applying the Page level filter and visual level filter based on the Latest Date, but issue is it's not responding to the slicer.
E.g: I applied page level filter to show the data for 16-04-2025, but it's not filtering. I tried using the visual level filter too but what it's doing is it filters my table visual only for the Latest Date, if I change the slicer to different date it's completely BLANK.
I have 3 pages for Timeliness, Completeness, Validation. I want to apply the same in all 3.
Can someone please help out in this on High Priority!!!
Show_Timeliness_Latest =
IF (
etl_timeliness_log[Timeliness_ETLDate_Only] = [LatestDate_Timeliness],
TRUE(),
FALSE()
)
Timeliness_LatestDate =
CALCULATE (
MAX ( etl_timeliness_log[Timeliness_ETLDate_Only] ),
REMOVEFILTERS(etl_timeliness_log)
)
Timeliness_IsLatest =
IF (
MAX(etl_timeliness_log[Timeliness_ETLDate_Only]) = [Timeliness_LatestDate],
1,
0
)
Help me in step by step procedure. I used all the AI tools but no luck.
Thank You
Pradeep
Hi cmkp2675 ,
Try this approach:
Instead of filtering pages or visuals, control which data is shown through measures.If the user selects a date via slicer → that date is used.
If the user does not select any date → the latest date is used automatically.
Dont give any visual/page level filters instead use dynamic measure for this-
EffectiveDate =
VAR SelectedDate = SELECTEDVALUE(MetricsData[Date])
VAR LatestDate = CALCULATE(MAX(MetricsData[Date]), ALL(MetricsData))
RETURN
IF(ISBLANK(SelectedDate), LatestDate, SelectedDate)If you have Timeliness, Completeness, Validation on separate pages, just replicate the same logic for each dataset (change the table/column names accordingly).
Below is the screenshot and pbix file for detailed informationIf this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
8 Replies
cmkp2675 , Hope you are using a dimension Table. Create New column in the date table
Latest = if ([Date] = Max(Table[Date]), 1, 0)
Use this as page level filter
If there are more than one table
Latest = if ([Date] = Max( Max(Table[Date]),Max(Table1[Date]) ) 1, 0)
or
Latest = if ([Date] = MaxX( {Max(Table[Date]),Max(Table1[Date]), ,Max(Table2[Date])}, [Value] ) 1, 0)
Use this in the Page level filtervery similar to Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA
Hi amitchandak
Thank You for your response.The issue is if we apply the visual or page level filter it's not responding to the external SLICER. In this case could you please go through the screenshot I attached? In data the there are multiple dates but once the visual level filter or Page level is applied, if we want to see the data of different dates it won't work. That's my biggest issue. Please help me to resolve.
LatestDate = CALCULATE(MAX(MetricsData[Date]), ALL(MetricsData))
Column DAX:
IsLatest = IF(MetricsData[Date] = [LatestDate], 1, 0)
Thank YouPradeep
Hi cmkp2675 ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.
To make sure your Power BI visuals always show the latest date's data, follow these steps. First, load your data into Power BI. Next, create a measure to find the latest date with this formula:create measure in your table
LatestDate = CALCULATE(MAX(MetricsData[Date]), ALL(MetricsData))
MetricsData is the table name . Then, create a column to mark the latest rows with this formula:create column
IsLatest = IF(MetricsData[Date] = [LatestDate], 1, 0)Now, add a table visual and include the fields Date, Department, Metric, and Value. In the Filters pane, drag the IsLatest column and set the filter to is 1, then apply it. This will make sure only the latest date's data is shown in your visual.
To apply this to other pages, simply copy the visuals from page 1 and paste them into page 2. Ensure the same IsLatest = 1 filter is applied on the new page too.
Finally, if you want to add a new row manually, go to Power Query, create a new row, and then use the Append Queries feature to combine the old data with the new row. Make sure the measures and columns are updated to include the new data. After refreshing the data, all your visuals will automatically update to show the latest data, including the newly added row.
Please go through the screenshot and pbix file which i have shared for more clarification:
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.Hi v-menakakota,
Thank You for your response.The issue is if we apply the visual or page level filter it's not responding to the external SLICER. In this case could you please go through the screenshot I attached? In your data the there are multiple dates but once the visual level filter or Page level is applied if we want to see the data of different dates it won't work. That's my biggest issue. Please help me to resolve.
LatestDate = CALCULATE(MAX(MetricsData[Date]), ALL(MetricsData))
Column DAX:
IsLatest = IF(MetricsData[Date] = [LatestDate], 1, 0)
Thank YouPradeep
Hi cmkp2675 ,
Try this approach:
Instead of filtering pages or visuals, control which data is shown through measures.If the user selects a date via slicer → that date is used.
If the user does not select any date → the latest date is used automatically.
Dont give any visual/page level filters instead use dynamic measure for this-
EffectiveDate =
VAR SelectedDate = SELECTEDVALUE(MetricsData[Date])
VAR LatestDate = CALCULATE(MAX(MetricsData[Date]), ALL(MetricsData))
RETURN
IF(ISBLANK(SelectedDate), LatestDate, SelectedDate)If you have Timeliness, Completeness, Validation on separate pages, just replicate the same logic for each dataset (change the table/column names accordingly).
Below is the screenshot and pbix file for detailed informationIf this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
