Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |