Forum Discussion
Responsive ResidentReturn blank value on DATEDIFF calculated column
HI. I have a few date columns in my fact table - request submission date, intake date, and review date. Using DATEDIFF I am calculating days to intake and days to review. When a request lacks an intake date and/or review date, that request doesn't show up in my table visualization. How can I show all requests regardless of whether they have data in my calculated Days to Intake column?
Sample data
| Request | Type | Request Title | Submission Date | Intake date |
| 80 | PM Support | PIPP Assistance | 5/31/2022 10:45 | 6/8/2022 0:00 |
| 81 | DA/DS Support | Mont substance and tobacco use interventions | 6/2/2022 13:01 | 6/9/2022 0:00 |
| 82 | PIC Support | Contract Summary Report | 6/7/2022 12:12 | 6/9/2022 0:00 |
| 83 | PIC Support | 6/7/2022 13:59 | ||
| 84 | PIC Support | Decrease CDI Denials | 6/15/2022 6:25 | 6/16/2022 0:00 |
| 85 | DA/DS Support | Midazolam dosing in EP Patients | 6/16/2022 11:45 | 6/17/2022 0:00 |
| 86 | PIC Support | Downtime Impact on Operations | 6/20/2022 6:23 | 6/21/2022 0:00 |
| 87 | DA/DS Support | Pivoting MSO master roster files | 6/21/2022 12:21 | 6/23/2022 0:00 |
| 88 | DA/DS Support | PI Opportunities for Lost and Found Process | 6/23/2022 8:58 | 6/23/2022 0:00 |
DAX for Days to Intake calculated column
Days to Intake = DATEDIFF(Requests[Submission Date],Requests[Intake date],DAY)
Here is what a table visualiztion looks like without including Days to Intake
When I add Days to Intake, Request #83 disappears:
I did try to adjust the DAX for the calculated column but that didn't help.
Days to Intake2 =
IF(
NOT(ISBLANK(Requests[Intake date])),
DATEDIFF(Requests[Submission Date],Requests[Intake date],DAY),
BLANK()
)
So, is there a setting in the visualization I can use to show such rows or is there some way I should adjust my DAX or something else? Any help would be greatly appreciated!
cathoms , I have found an even easier way to achieve what you want, with no change to your existing measure. In the table visual, in the Values section, right-click on the Request field and select "Show Items With No Data". When you do that, the row for Request 83 will appear.
More information here
Show items with no data in Power BI - Power BI | Microsoft Docs
7 Replies
cathoms , you can create a measure that still returns a value if the IntakeDate is blank, like this.
Days to Intake = SWITCH(true() ,ISBLANK(SELECTEDVALUE('YourTable'[Intake date])), "" ,DATEDIFF( SELECTEDVALUE('YourTable'[Submission Date]) ,SELECTEDVALUE('YourTable'[Intake date]) ,DAY ) )This gives me these results:
cathoms , I have found an even easier way to achieve what you want, with no change to your existing measure. In the table visual, in the Values section, right-click on the Request field and select "Show Items With No Data". When you do that, the row for Request 83 will appear.
More information here
Show items with no data in Power BI - Power BI | Microsoft Docs
- parry2k
Super User
cathoms you are the missing the fundamentals here, reason show items with no data worked because the column was getting aggregated (implicit measures) since there is no aggregation required there is no use of show items with no data
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
