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
dscott73
Resolver I
Resolver I

How to handle NULL date value in fact table

Ideally one would not have NULL date values in the fact table.

 

 

I havent seen any yet, but in the DateDim table for a project I am working on, they have two rows. One is 1/1/1900 this one looks to cover NULL values.  Then they have 1/1/9999, this is used to fill in an "Expired" column.  The 1/1/9999 appears to cover the "Not Expired" date.

 

The current "valid" dates run from 1/1/2000 through 12/31/2030.

 

When I put a date slicer on the report and pull the Date over from the DateDim, and the slicer is set to "Between", it looks like the slicer is attempting to "fill in" the dates between year 2030 and year 9999.  This also appears to be fowling up the SAMPERIODLASRYEAR function. I get an error about an invalid numeric representation of a date.

If I remove the 1/1/9999 row from the DateDIm table, then the slicer wants to "fill in" the dates from 1/1/1900 to 1/1/2000.

 

That's also annoying.  

 

What is the proper way to handle NULL dates?  I have read it is to use the 1/1/1900 date, but this seems to fowl up Power BI date usages.

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

 

If you already know the valid dates, then go to Modelling > New Table and write this measure there

 

=CALENDAR(DATE(2000,1,1),DATE(2030,12,31))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-juanli-msft
Community Support
Community Support

Hi @dscott73 

If i understand you right, 

To show a valid date period in the "between" date slicer, you could create a calcuated column to define which date is valid/null/expired.

Column =
SWITCH (
    TRUE (),
    [date] = DATE ( 1900, 1, 1 ), "null",
    [date] = DATE ( 9999, 1, 1 ), "expired",
    [date] >= DATE ( 2000, 1, 1 )
        && [date] <= DATE ( 2030, 12, 31 ), "valid"
)

 

click on the slicer and select "filter" on another slicer.

6.png

 

If not,

What expected result do you want? Could you show an example with your scenario?

Addtionally, please check this article to see if it helps you.

https://www.sqlbi.com/articles/blank-handling-in-dax/

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-juanli-msft

 

This would work and eliminate the issue.  Since the record was only needed for the dataware house, i just filtered it from the dataset, leaving the "1/1/1900" row for "nulls".

 

My expectation, would be that the slicer would only include contiguous dates, or only the dates actually found in the Date dimension table. I see the issue with the "Between" function of the slicer, it is getting the MIN/MAX and filling in everything between.  I get it, but maybe the ability to shut that off in the slicer controls so the slicer uses only the dates in the Date Dimension. 

Hi @dscott73 

" "Between" function of the slicer getting the MIN/MAX and filling in everything between" 

After test, it seems a design-behaviour. We can't reset it from user-side, please create an idea here so thie feature may be improved in feature.

Though, to acheive your goal we could work with some workarounds.

Besides my previous post and your method, here is another workaround.

create a new table with the function:

Date = FILTER(CALENDARAUTO(),[Date]>=DATE(2000,1,1)&&[Date]<=DATE(2030,12,31))

then create relationship between this new date table and your table.

when add "date" from the new created date table

2.png

 

 

Does my previous post or this post really help? 

If it does, could you kindly accept it as a solution so others may find it quicklly?

 

Best Regards

MaggieSmiley Happy

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.