How to use Dates Table in Power BI reports

[Anonymous]

Hi all,

I have problem that I cannot figure out how to resolve it. I have created a calendar table in my Power BI model and linked it to diffrent tables via date column.

For creating visuals I have the option to either use date column from dates table or date column from individual tables. I thought the best practice is to always use date column from dates table however since Dates table contains a larger range of dates compare to an indivdual table when i use date column from dates table in visuals sometimes in distorts the visual and shows all the dates even if there is no match for it in the induvidual table ( shows rows with 0 values) . I noticed this happens when I use a DAX measure in the visual, but when I use a column from the table (without any dax measure) it correctly shows only the rows that have values ( dates within the table's data range even though I use Dates date column in the visual).

Sorry for the long description of the issue. I hope I was able to explain it clearly.

I would appreciate for any help explianing why this happens.  

[v-lili6-msft]

hi  @Anonymous 

First, for your case, you'd better use date column from dates table. 

https://radacad.com/do-you-need-a-date-dimension

Second, for why " I noticed this happens when I use a DAX measure in the visual, but when I use a column from the table (without any dax measure) it correctly shows only the rows that have values ( dates within the table's data range even though I use Dates date column in the visual)."?

You should know that 1. measure will calculate expression on each row context of this visual, and when you drag a column, it will only calculate simple aggregation(SUM, COUNT, MIN...), this won't related to other row context.

If there is no value on one day, use a column, it will return blank on that date, but use a measure, it may return "0" or blank or other value based on the current row context on that date. So if it does not return blank on that date, it will show the date and value in the visual.

And for your case, you could add a IF conditional in the measure as below to solve it as below:

Measure=IF(<expression>=0, Blank(), <expression>)

and you could also add the conditional in the measure Filter as this to get it:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

By the way, this document may help you too:

https://docs.microsoft.com/en-us/power-bi/desktop-show-items-no-data

Regards,

Lin

[mwegener]

Hi @Anonymous ,

the columns from the date table are the right choice.

Can you provide a sample pbix and you measure definition?

[Anonymous]

Thank you for the reply. I don't see an option to attach pbix file here , but the idea is very simple.

For exmaple we just need to create a table with date and sales amount ( only a few rows)  and a calendar table ( with a larger date range . e.g. one year ) and link two tables together.  

then add two table visuals and a test meassure like :

Measure = PRODUCTX (Sheet1 , Sheet1[Sales] ) -1  

Now use this measure along with date  column from sales table in one visual and date column from Calendar table in another visual. You will see that when using date column from sales table the visual correctly shows only the rows with sales data however if you use date column from calendar table it will show all the rows in calendar table even though there is no sales data for those days.

[mwegener]

Hi @Anonymous ,

the question is "Why do you create your measure so that it always returns a value?"

Maybe this helps:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

[Anonymous]

To answer your question ( assuming I undersstand correctly what you are asking ) becaause there is no data in sales table for those dates in Calendar table.

This is actually very common case when you Calendar table has much larger range of date than an individdual table that is linked to it and when we use date calum from calendar table in visuals then visuals don't filter out dates with no coressponding data and they show rows for all dates in Calendar table dates.  

[mwegener]

Hi @Anonymous ,

I am a bit confused.
You only want to see dates that also have a value in the fact table. Right?

So the measure for dates without value must return BLANK().

[Anonymous]

Yes, but you don't want to see those blanks, because if you use the measure lets say in a line chart it will distort the chart. You want to only return rows with data. The solution is to use date from sales table and not from calendar table but I thought the best practice is to always use dates from calendar table!!? 

[mwegener]

Hi @Anonymous,

can you share your measure definition?

If there is no value in the fact table, the measure must also return BLANK () and no -1.

For charts with a timeline we can use the option "show items with no data"

https://docs.microsoft.com/en-us/power-bi/desktop-show-items-no-data