Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

@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

14 REPLIES 14
tamerj1
Super User
Super User

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

@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 , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
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 , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
PC2790
Community Champion
Community Champion

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
Super User
Super User

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.

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

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 workCapture.PNG

@amrhitch 

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. 

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

@rush 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.