Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 :
Solved! Go to Solution.
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:
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! 🙂
Proud to be a Super User!
Hi @EF
I started with this table:
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:
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! 🙂
Proud to be a Super User!
Thanks @JarroVGIT for your quick response!
I got this error message: A table of multiple values was supplied where a single value was expected.
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.
Proud to be a Super User!
@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.'
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)
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:
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! 🙂
Proud to be a Super User!
It works! Thanks so much! Useful for so many things.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
17 | |
17 | |
16 |
User | Count |
---|---|
28 | |
27 | |
18 | |
14 | |
14 |