Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am looking to fil down a column based on a Customer Reference column in order to fill null entries with the last non blank entry for that customer which would look like the fixed_status column
Sample:
Customer-Ref | Date | Status | Fixed_Status |
A | 31/12/2015 | null | Null |
A | 01/01/2016 | Hold | Hold |
A | 01/01/2016 | null | Hold |
A | 03/01/2016 | null | Hold |
A | 04/01/2016 | Active | Active |
A | 05/01/2016 | null | Active |
A | 06/01/2016 | Hold | Hold |
A | 07/01/2016 | null | Hold |
B | 08/01/2016 | Active | Active |
B | 09/01/2016 | null | Active |
B | 10/01/2016 | Hold | Hold |
B | 11/01/2016 | null | Hold |
From some research i think i need to use lastnonblank with a filter based on the customer ref like:
https://community.powerbi.com/t5/Desktop/Filling-Data-Gaps-Conditionally/td-p/148745
but this soultion does not completely fit my needs as an example for customer A on the 7th is filled as "Active" rather than "Hold", same for customer B on the 11th filled as "Active" rather than "hold"
im not sure if it complicates the situation in that a customer can have more than one entry on a particular date.
I am new to DAX
Thank you for any help
You can use this column expression to get your result. You may need to change "null" to "", if they are actually null values (vs. the word null).
NewStatus =
VAR vThisDate = 'Status'[Date]
RETURN
CALCULATE (
LASTNONBLANKVALUE (
'Status'[Date],
MIN ( 'Status'[Status] )
),
ALLEXCEPT (
'Status',
'Status'[Customer-Ref]
),
'Status'[Date] <= vThisDate,
'Status'[Status] <> "null"
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |