Using ISBLANK Adding and error note to blank rows

DAX DAX Calculations
1

Please can you help me fix my formula. I am trying to add an error message to rows that do not have a date

I am calculating how long a patient waits between being referred and the start of the service the LOW calculation works fine and is below

SUMX(tbl_HomeFirst,DATEDIFF(tbl_HomeFirst[Referral Date],tbl_HomeFirst[Reablement Start Date],DAY))

Now I want to add an ISBLANK test on the Referral date so that it sends back a text note instead of a blank space

Here is my formula:
=IF(
ISBLANK(tbl_HomeFirst[Referral Date]),
“Date Missing”,
SUMX(tbl_HomeFirst,DATEDIFF(tbl_HomeFirst[Referral Date],tbl_HomeFirst[Reablement Start Date],DAY))

But its not working as I get an error message below
"The expression is not valid or appears to be incomplete. Please review and correct the expression. The end of the input was reached."

2

Hi
can share the PBIX file?

lb

3

Unfortunately I am not allowed to share the file as it contains confidential patient data. I was hoping experts could help me find the problem based on the code above I can send Photo of the file if that helps

4

Hi @Ducati. I’d check with a zero instead of the “Date Missing” text string; I’d guess DAX falls down when you use two different data types for the THEN and ELSE of an IF statement.
Greg

5

Hi @Ducati, we’ve noticed that no response has been received from you since the 20th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

6

Hi Greg
I tried the formula as you recommended but its still not happy
This is the error message i get now DAX Error message

7

@Ducati Can’t call a column inside ISBLANK when you are creating a measure as a measure doesn’t have a row context by default.

Try :

=
IF (
    ISEMPTY ( VALUES ( tbl_HomeFirst[Referral Date] ) ),
    "Date Missing",
    SUMX (
        tbl_HomeFirst,
        DATEDIFF (
            tbl_HomeFirst[Referral Date],
            tbl_HomeFirst[Reablement Start Date],
            DAY
        )
    )
)
8

Hi Antriksh

Awesome thank you so much - that worked just great and thank you for the ISBLANK comment - I am still making the transition from Excel formula to DAX and its a process to think in columns instead of rows

Really really helpful - thank you