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.
Hi All
I need help trying to fill values down based on the date I have for each staff.
Please see the example below with the expected column to be created along with the link to the sample data:
Sample Data for Data Fill Down
Thanks in advance.
Solved! Go to Solution.
Please try
Employment Status New =
VAR CurrentDate = TableName[Date]
VAR CurrentStatus = TableName[Employment Status]
VAR CurrentIdTable =
CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR NoBlanksTable =
FILTER ( CurrentIdTable, TableName[Employment Status] <> BLANK () )
VAR NoBlanksTableBefore =
FILTER ( NoBlanksTable, TableName[Date] < CurrentDate )
VAR LastDateWithData =
MAXX ( NoBlanksTableBefore, TableName[Date] )
VAR LastStatus =
MAXX (
FILTER ( NoBlanksTableBefore, TableName[Date] = LastDateWithData ),
TableName[Employment Status]
)
RETURN
IF ( ISBLANK ( CurrentStatus ), LastStatus, CurrentStatus )
Just wanted to add that my code retrieves the last available value before the blank incase you have multiple available values per Id with blanks inbetween the values.
@tamerj1 Thank you but I forgot to mention that staff can have multiple employment status which needs to fill in until the next one if there is. Currently, it does not do that.
@rush Easy solution is below:
@rush Try this column
Hey @rush ,
Here, try this:
New Value =
VAR LastNonBlankID =
CALCULATE (
LASTNONBLANK ( FillDown[ID], 1 ),
FILTER (
ALL ( FillDown),
FillDown[ID] <= EARLIER ( FillDown[ID])
&& NOT ( ISBLANK ( FillDown[Employment Status] ) )
)
)
RETURN
CALCULATE (
Max ( FillDown[Employment Status] ),
FILTER ( ALL ( FillDown ), FillDown[ID] = LastNonBlankID )
)
Outcome:
@PC2790 Thank you but I forgot to mention that staff can have multiple employment status which needs to fill in until the next one if there is. Currently, it does not do that.
Hi @rush
you can use
Employment Status New =
VAR CurrentStatus = TableName[Employment Status]
VAR CurrentIdTable =
CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR NoBlanksTable =
FILTER ( CurrentIdTable, TableName[Employment Status] <> BLANK () )
VAR LastDateWithData =
MAXX ( NoBlanksTable, TableName[Date] )
VAR LastStatus =
MAXX (
FILTER ( NoBlanksTable, TableName[Date] = LastDateWithData ),
TableName[Employment Status]
)
RETURN
IF ( ISBLANK ( CurrentStatus ), LastStatus, CurrentStatus )
@tamerj1 Thank you but I forgot to mention that staff can have multiple employment status which needs to fill in until the next one if there is. Currently, it does not do that.
Please try
Employment Status New =
VAR CurrentDate = TableName[Date]
VAR CurrentStatus = TableName[Employment Status]
VAR CurrentIdTable =
CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR NoBlanksTable =
FILTER ( CurrentIdTable, TableName[Employment Status] <> BLANK () )
VAR NoBlanksTableBefore =
FILTER ( NoBlanksTable, TableName[Date] < CurrentDate )
VAR LastDateWithData =
MAXX ( NoBlanksTableBefore, TableName[Date] )
VAR LastStatus =
MAXX (
FILTER ( NoBlanksTableBefore, TableName[Date] = LastDateWithData ),
TableName[Employment Status]
)
RETURN
IF ( ISBLANK ( CurrentStatus ), LastStatus, CurrentStatus )
Hi Tamerj,
I tried your solution and it was working well all over the table but for some reason when the before and after status 'is_available' is ZERO, the calculated column doesn't work
I had an extensive look at it and still not sure if the issue is a result of the engine confusion between 0 and blank. It seems to me that you need to addd the [MAX] and [MIN] columns to the ALLEXCEPT arguments. Please do that and let me know if it works.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |