Forum Discussion
Page-Level Filtering based on the Latest Date
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.
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 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.
Hi cmkp2675 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a Kudos so other members can easily find it.
Thank you.Hi cmkp2675 ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.