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?
User | Count |
---|---|
83 | |
69 | |
68 | |
65 | |
54 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |