cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rush
Helper V
Helper V

Fill down values with DAX calculated column

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:

rush_0-1653389830942.png

Sample Data for Data Fill Down 

Thanks in advance. 

 

1 ACCEPTED SOLUTION
tamerj1
Community Champion
Community Champion

@rush 

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 )

View solution in original post

12 REPLIES 12
tamerj1
Community Champion
Community Champion

@rush 

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.

tamerj1
Community Champion
Community Champion

@rush 

This is exactly what it does

Tahreem24
Super User
Super User

@rush Easy solution is below:

Capture.JPG

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
Tahreem24
Super User
Super User

@rush Try this column

Column 2 = CALCULATE(MAX('Table'[Status]),ALLEXCEPT('Table','Table'[ID]))
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
PC2790
Super User
Super User

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_0-1653392598148.png

 

@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.

tamerj1
Community Champion
Community Champion

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.

tamerj1
Community Champion
Community Champion

@rush 

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 )

Thank you very much. @tamerj1  It works really well. 🙏

tamerj1
Community Champion
Community Champion

@rush 

Ok I guess you are right. Let me try to fix it

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors