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
team_steve
Advocate I
Advocate I

null = blank = 0 in relasionships

Hello, I hope I can explain this properly. I've created a dimension table for dates in SQL and import this into Power BI. I have lot of 'relative' columns in the table which are defined something like this. DATEDIFF(month, invoice_date, date()).

With this in place I can use the filtering in Power BI to look at invoice_dates from last month - i.e. where invoice_date = -1

To look at the current month I filter the invoice_date = 0, and next month would be invoice_date = 1. Works well- mostly.

Problem is that orders with on invoice_date of null (not invoiced) appear when I filter to invoice_date = 0.

It's like Power BI simplifying null to 0 and we all know that null <> 0. Strangely I can remove the values from the filtered result by adding a second filter to remove BLANK. So I guess null = blank = 0.
I don't know how resolve this.

The only solution I can think of at the moment is to fudge the DATEDIFF calculcation so that last month = -1, this month = 1 and next month =2, and so invoice_date = 0. This might be OK I guess but it feels a bit messy. I'd rather this month=0, no invoice = BLANK and then you have 1 month ahead, 2 months ahead etc

 

1 ACCEPTED SOLUTION
mattbrice
Solution Sage
Solution Sage

Can you use time intelligence functions instead?

 

And here is good article on BLANK and NULL in Dax.  

 

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

 

View solution in original post

2 REPLIES 2
mattbrice
Solution Sage
Solution Sage

Can you use time intelligence functions instead?

 

And here is good article on BLANK and NULL in Dax.  

 

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

 

Thanks for the reply.
The link to the article and BLANK is excellent. SQL NULL becomes BLANK. BLANK is converted to 0 when evaluated. Therefore NULL is BLANK is 0, effectively NULL is 0. I'm not going to use the word equals.

I think I would find this easier to accept if my BLANK numbers appeared as 0 in the Card Visual. Here I need to check for IsBlank. I guess I'm displaying rather than evaluating, though some might argue that can displaying you are evaluating.

Anyway, I know more about BLANK than I did yesterday and will change my processes to handle this. Thank you.

 

You suggest using the time intelligence functions rather than my dates dimension - I thought I was doing things in the correct way. Do you have any good links to explain the functions?

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.