cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EF Helper I
Helper I

fill in blank values from most recent row for specific ID

Hi all,

 

I have been searching this for a while.

I need to fill in missing data based on rows before for the specific ID.

The rows are records of events for each patient (admit date, appointments, discharge...)

Depending on the event, sometimes alot of other data is missing.

For example, every appointment row has the patients' current diagnosis at the time of the appointment. However, discharge events have no diagnosis associated. I want to fill the empty diagnosis with the patients most recent diagnosis.

 

I tried : 

 

IF (
Combined_Queries[DiagnosisKey1] = BLANK (),
CALCULATE (
LASTNONBLANK ( Combined_Queries[DiagnosisKey1], Combined_Queries[DiagnosisKey1] ),
FILTER(ALLEXCEPT ( Combined_Queries, Combined_Queries[account] ),Combined_Queries[date]<=earlier(Combined_Queries[date]))),
 
Combined_Queries[DiagnosisKey1]
)
 
Combined_Queries is the name of the table, DiagnosisKey1 is the column missing values, account is the ID for each patient, date is date of the event.
 
This fills in the first blank correctly, but every other blank gets filled with that same value. I need it to change based on the most recent event for that date.
 
Query Editor fill down doesn't help either because it fills down regardless of the account number.
 
Any ideas?
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: fill in blank values from most recent row for specific ID

Hi @EF ,

Well, how can PBI know which DiagnosticKey1 needs to be used then if the Account and Date can be the same? So let's say someone (Account A)has an appointment on Jan 1st for the Flu and later that day for the Cold. Then, on Jan 2, the DiagnosticKey1 is empty. What would the fill be, Flu or Cold?

If it doesn't matter, you can use the following as a calculated column:

FilledDiagnosisKey = 
IF(Combined_Queries[DiagnosisKey1] = BLANK(),
    VAR _curAccount = Combined_Queries[Account]
    VAR _curDate = Combined_Queries[Date]
    VAR _filteredTable = FILTER(Combined_Queries, Combined_Queries[Account] = _curAccount && Combined_Queries[DiagnosisKey1] <> BLANK() && Combined_Queries[Date] < _curDate)
    VAR _maxDate = MAXX(_filteredTable, Combined_Queries[Date])
    RETURN
    MAXX(FILTER(_filteredTable, [Date] = _maxDate && [Account] = _curAccount), [DiagnosisKey1])
, Combined_Queries[DiagnosisKey1])

In my original test table, I added a row for Jan 1st, Account A with DiagnosticKey1 = Sick. The result is this:

image.png

Let me know if this helps you. Don't forget to give kudo's for support 🙂

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

 


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Super User I
Super User I

Re: fill in blank values from most recent row for specific ID

Hi @EF 

I started with this table:

 image.png

Then I used the following calculated column (this is not optimized but it is easier to read and understand the logic):

FilledDiagnosisKey = 
IF(Combined_Queries[DiagnosisKey1] = BLANK(),
    VAR _curAccount = Combined_Queries[Account]
    VAR _curDate = Combined_Queries[Date]
    VAR _filteredTable = FILTER(Combined_Queries, Combined_Queries[Account] = _curAccount && Combined_Queries[DiagnosisKey1] <> BLANK() && Combined_Queries[Date] < _curDate)
    VAR _maxDate = MAXX(_filteredTable, Combined_Queries[Date])
    RETURN
    LOOKUPVALUE(Combined_Queries[DiagnosisKey1], Combined_Queries[Account] , _curAccount, Combined_Queries[Date] , _maxDate),
    Combined_Queries[DiagnosisKey1])

Result is:

image.png

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

EF Helper I
Helper I

Re: fill in blank values from most recent row for specific ID

Thanks @JarroVGIT  for your quick response!

 

I got this error message: A table of multiple values was supplied where a single value was expected.

Super User I
Super User I

Re: fill in blank values from most recent row for specific ID

You are adding a measure, but this dax needs to be a calculated column. A very important difference 🙂 You should get any errors if applying the DAX as a calculated column.


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

EF Helper I
Helper I

Re: fill in blank values from most recent row for specific ID

@JarroVGIT Hi I just got back on my computer...

I used a calculated column, still got that error message:

'A table of multiple values was supplied where a single value was expected.'

EF Helper I
Helper I

Re: fill in blank values from most recent row for specific ID

Maybe that error is because it is possible for a client to have more than 1 entry for a single date? The entries are appointments and they can have multiple appointments per day (for different services)

Highlighted
Super User I
Super User I

Re: fill in blank values from most recent row for specific ID

Hi @EF ,

Well, how can PBI know which DiagnosticKey1 needs to be used then if the Account and Date can be the same? So let's say someone (Account A)has an appointment on Jan 1st for the Flu and later that day for the Cold. Then, on Jan 2, the DiagnosticKey1 is empty. What would the fill be, Flu or Cold?

If it doesn't matter, you can use the following as a calculated column:

FilledDiagnosisKey = 
IF(Combined_Queries[DiagnosisKey1] = BLANK(),
    VAR _curAccount = Combined_Queries[Account]
    VAR _curDate = Combined_Queries[Date]
    VAR _filteredTable = FILTER(Combined_Queries, Combined_Queries[Account] = _curAccount && Combined_Queries[DiagnosisKey1] <> BLANK() && Combined_Queries[Date] < _curDate)
    VAR _maxDate = MAXX(_filteredTable, Combined_Queries[Date])
    RETURN
    MAXX(FILTER(_filteredTable, [Date] = _maxDate && [Account] = _curAccount), [DiagnosisKey1])
, Combined_Queries[DiagnosisKey1])

In my original test table, I added a row for Jan 1st, Account A with DiagnosticKey1 = Sick. The result is this:

image.png

Let me know if this helps you. Don't forget to give kudo's for support 🙂

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

 


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

EF Helper I
Helper I

Re: fill in blank values from most recent row for specific ID

It works! Thanks so much! Useful for so many things. 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors