Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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/
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?
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |