Forum Discussion
How to filter a visual to only show the latest rows based on dataLake_CreatedTime
Body:
I’m working with a snapshot table in Power BI that refreshes frequently. The table has multiple rows for the same record ID, each with a different dataLake_CreatedTime (datetime) showing when that snapshot was created.
What I need:
For any given record ID, my visual should only show the row(s) with the most recent dataLake_CreatedTime.
If there are multiple records tied for the latest timestamp, I only want one of them shown in the visual.
Details:
Table name: api_openreqs
Key fields:
JobReq_JobReqID (text) — may contain duplicates because of multiple snapshots
dataLake_CreatedTime (datetime) — snapshot creation timestamp
Goal: Limit visuals to one latest row per JobReq_JobReqID based on the maximum dataLake_CreatedTime.
What I’ve tried:
Using MAX in a measure to find the latest date per ID
Creating a calculated column to flag the latest row
Running into issues with ties and overcounting
Question:
What’s the best way (measure, calculated column, or Power Query transformation) to configure my data so that visuals always filter to only the latest rows based on dataLake_CreatedTime?
3 Replies
- Greg_Deckler
Community Champion
kendomino Sounds like you need a Complex Selector: https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
Hi Greg_Deckler , thanks for the proposal, but that’s not quite what I’m looking for. I need my card visualization to show only the most recently updated data—specifically, the latest JobReq_JobReqId based on the DataLake_createdtime field. I’ve attached a screenshot for context.
- Greg_Deckler
kendomino So that's what I call a double lookup. It looks something like the following:
Measure = VAR __Table = ALLSELECTED( 'Table' ) VAR __MaxDate = MAXX( __Table, [DataLake_createdtime] ) VAR __Result = MAXX( FILTER( __Table, [DataLake_createdtime] = __MaxDate ), [JobReq_JobReqId ) RETURN __Result
