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
Anonymous
Not applicable

Dax to show flag when one date field is empty

Hello
I have a requirement with fields like release date, hire date for counting days in between two dates.
Now the Dax I created is
If(is blank([release date]))||(is blank([hire date])),blank(),datediff([hire date],[release date], day))
This is giving me blank row whenever I have empty row in either hire date or release date. I need to show an error message when I have a row with release date but no hire date.
So in calculated column with days, it would be

Empty hire date and empty release date will show blank
Empty release date only will show blank
Empty hire date only will show error
Any help on how to add that part in Dax?
Thanks
1 ACCEPTED SOLUTION

I apologize - I must not have read your entire request - 

 

Below I attempted to replicate the request you have asked for. 

 

2020-02-10_1235.png

 

This was acomplished by utilizing the below IF statement - 

Status = 
IF( 
    AND(
    ISBLANK(Sheet1[Release Date]),
    ISBLANK(Sheet1[Hire Date])),
        BLANK(),

IF(
    ISBLANK( Sheet1[Release Date] ),
        BLANK(),

IF(
    ISBLANK( Sheet1[Hire Date] ),
        "Error" )
))

 

Let me know if this works for you. If so throw me a thumbs up and mark as the solution.

 

Enjoy!

 

 

View solution in original post

7 REPLIES 7
AlexAlberga727
Resolver II
Resolver II

I hope im not incorrect. However - 

 

With PowerBI a column must contain the same data type. Meaning - a field which contains dates, cannot also contain text. You're error message may need to be contained in a seperate field.

 

I would make a calculation so that if you're returning a blank row - a calculated column beside it could simple be:

 

ERROR =
IF(
ISBLANK( [THE OTHER COLUMN] ),
"ERROR MESSAGE",
"")

 

Create a filter to only show errors, and you have an error report of sorts.

 

Let me know if this helps!

Anonymous
Not applicable

thanks
The new column I have with no of days difference only. So number column.
Can not I add text with it also. Do not want to add another column.

If it works, let me know! Also - If it works throw this post a thumbs up, and deem it as the solution. 

Anonymous
Not applicable

Any other way? In query editor or some other way to do it in one column?
Experts?

I apologize - I must not have read your entire request - 

 

Below I attempted to replicate the request you have asked for. 

 

2020-02-10_1235.png

 

This was acomplished by utilizing the below IF statement - 

Status = 
IF( 
    AND(
    ISBLANK(Sheet1[Release Date]),
    ISBLANK(Sheet1[Hire Date])),
        BLANK(),

IF(
    ISBLANK( Sheet1[Release Date] ),
        BLANK(),

IF(
    ISBLANK( Sheet1[Hire Date] ),
        "Error" )
))

 

Let me know if this works for you. If so throw me a thumbs up and mark as the solution.

 

Enjoy!

 

 

Anonymous
Not applicable

Thanks again. So how to merge my day difference calculated column with this one so that in one column user can see day difference in number in between two dates, blank cell when release day or both fields are missing and error message when hire day is missing but release day is there. No way to show in one column?
Any workaround?
Thanks much
Anonymous
Not applicable

I ended up creating 2 columns and then concatenation them into one. Data type changed to text but will show to user and see what they say. Thanks much

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.

Top Solution Authors