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
cathoms
Helper V
Helper V

Return 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

RequestTypeRequest TitleSubmission DateIntake date
80PM SupportPIPP Assistance5/31/2022 10:456/8/2022 0:00
81DA/DS SupportMont substance and tobacco use interventions6/2/2022 13:016/9/2022 0:00
82PIC SupportContract Summary Report6/7/2022 12:126/9/2022 0:00
83PIC Support 6/7/2022 13:59 
84PIC SupportDecrease CDI Denials6/15/2022 6:256/16/2022 0:00
85DA/DS SupportMidazolam dosing in EP Patients6/16/2022 11:456/17/2022 0:00
86PIC SupportDowntime Impact on Operations6/20/2022 6:236/21/2022 0:00
87DA/DS SupportPivoting MSO master roster files6/21/2022 12:216/23/2022 0:00
88DA/DS SupportPI Opportunities for Lost and Found Process6/23/2022 8:586/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

cathoms_0-1656520721662.png

When I add Days to Intake, Request #83 disappears:

cathoms_1-1656520797178.png

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!

 

 

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@cathoms when you are using this calculated column in the visual, can you make sure it is not getting aggregated. Maybe the aggregation is changed to sum and for that reason the column with the blank value is not showing up.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

Anonymous
Not applicable

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

EylesIT_1-1656523506355.png

More information here

Show items with no data in Power BI - Power BI | Microsoft Docs

 

View solution in original post

7 REPLIES 7
parry2k
Super User
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 YouTube.gif 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks @parry2k. I didn't mention this in my OP but I think I do need the aggregation in order to show the average Days to Intake or Days to Reivew for my value totals, like this:

cathoms_0-1656526063450.png

 

 

cathoms
Helper V
Helper V

Thanks to both @parry2k and @Anonymous !

Days to Intake was aggregating so checking "don't summarize" worked. I also tested the "show items with no data" and that worked as well.

parry2k
Super User
Super User

@Anonymous sorry but not sure if your answers are addressing the issue, @cathoms is adding datediff as a column not a measure. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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

EylesIT_1-1656523506355.png

More information here

Show items with no data in Power BI - Power BI | Microsoft Docs

 

Anonymous
Not applicable

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

EylesIT_0-1656522920135.png

 

 

parry2k
Super User
Super User

@cathoms when you are using this calculated column in the visual, can you make sure it is not getting aggregated. Maybe the aggregation is changed to sum and for that reason the column with the blank value is not showing up.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.