Forum Discussion
Remove Extra Date Slicers
I don't think date tables will work. There is missing dates and duplicate dates in the query.
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
- Anonymous6 years agoNot applicable
Ok I was able to set up a date table however now I cant use my other slicer as "That would introduce ambiguity" Is there a way I can do both?
- edhans6 years ago
Community Champion
Yes. Need to see your model though. You are connecting tables in a way that doesn't have a clear and distinct path between tables. Again, Star Schema. Sounds like you have a SnowFlake Schema.
Post pics, or a link to a PBIX file that has confidential data removed.
- edhans6 years ago
Community Champion
Just wondering if you've made progress with this Anonymous