Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
106 | |
88 | |
82 | |
76 | |
73 |
User | Count |
---|---|
112 | |
103 | |
96 | |
74 | |
67 |