Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello there,
I'm having some trouble on solving this one.
To give a breif example, we have a sequence from 1 - 5 each with a status and date. Sometimes the user skips this sequence and now I'd like to fill the gap with the latest date available.
Example
ID | Status | StatusID | LatestDate |
025755 | Intial Lead | 1 | Jan 1 2021 |
025755 | Regular Lead | 2 | Jan 5 2021 |
025755 | Hot Lead | 3 | To be filled |
025755 | Signature Pending | 4 | To be filled |
025755 | Success | 5 | Jan 25 2021 |
Example2
ID | Status | StatusID | LatestDate |
025755 | Intial Lead | 1 | Jan 1 2021 |
025755 | Regular Lead | 2 | To be filled |
025755 | Hot Lead | 3 | 14 Jan 2021 |
025755 | Signature Pending | 4 | |
025755 | Success | 5 |
Now as you can see we have 2 statuses in example 1 that were skipped and thus have no dates. My objective is to fill these blank spaces for each group(ID) with the latest date available based on the max status/statusID.
In example 2, 1 status was skipped and the latest current Id is 3, so we only need to fill the one in between.
By default, status(ID) 1 always has a date.
Thanks!
Solved! Go to Solution.
@Anonymous , a new column
column =
var _max = maxx(filter(Table,[ID] = earlier([ID]) && not(isblank([Latest Date])) ) ,[Latest Date])
return
if(isblank([Latest Date]), _max, [Latest Date])
@Anonymous , a new column
column =
var _max = maxx(filter(Table,[ID] = earlier([ID]) && not(isblank([Latest Date])) ) ,[Latest Date])
return
if(isblank([Latest Date]), _max, [Latest Date])
Hi there @amitchandak
Thank you so much, this actually worked to do what I require. I'm still new to PBI, and still finding my way around working with row context.
Could you breifly explain the logic of compare with earlier ID?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |