Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |