Forum Discussion
Remove Extra Date Slicers
I have this report that I need to filter by date. There are three odbc queries in this report. Each one of these date filters is from a different report. I have tried making a relationship between them but whenever I delete one of them the data gets messed up and no longer shows the correct data. What am I doin wrong?
6 Replies
You should have ONE date table, and use only the dates from that table in all measures, slicers, visuals, etc. Trying to relate multiple date fields from different tables to other tables, besides the date table, would be a nightmare.
In my models, all date fields from any tables not in the Date table itself are hidden from the report view so I cannot accidentally use them.
See this article. If you need help creating a date table, or how to get everything in one place, ping back.
That is why you need a date table. It has no missiing dates, and becomes the 1 to many with other tables that can have missing or duplicated dates.
There is not a model in existence for Power BI that has date that cannot work with a date table. You might want to read up on a Star Schema, and a date table is a core component of this if the model has any dates.
Without a date table, you are going to continue to have difficult making it work as you want, and you may even discover some things are simply not possible without it.
Try it.
- Create a date table as per below, or for now, just use CALENDAR() and add custom columns (which I generally recommend against - see below)
- Mark it as a date table
- Turn off automatic date/time intelligence in options so it exclusively uses your date table
- Link all date filelds you care about to the Date column of your date table. Note: Only one can be active for each table. That's ok. You can activate others with the USERELATIONSHIP() modifier in a CALCULATE() if you need to use some date math off of a different field (Shipping date vs Sold date for example in a sales data table)
- Use ONLY the Date field or other fields in the Date table (year, quarter, whatever) in your measures and visuals.
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query
