Forum Discussion
One date slicer for multiple date columns
Hello everybody!
I'm new to posting on forum here, but definitely a veteran at checking this space for all PowerBI questions I have. That being said - I have not been able to find an answer to a burning question I have, or maybe I've written the wrong search terms... anywho...
How can I use just one date slicer on the page to filter multiple data sets that have multiple date columns of their own?
I'm working with multiple CRM-created data sets and pretty much all of them have at least 2 date columns. How do I 'tell' the date slicer which columns to filter by in each of my data sets?
I'm reading up now on creating special date tables and multiple fact tables, but so far have not figured it out yet... any help would be appreciated! 🙂
A
The fundamental issue is that you don't want to have multiple possible filtering paths from one table to another since this creates model ambiguity.
For example, if following relationship paths, Dates filters SentEmails and SentEmails filters EmailEvents, then having Dates directly filter EmailEvents via a relationship introduces ambiguity, which is why Power BI sets these relationships to be inactive.
There's no simple fix to create a good data model. The resolution is to structure your model to avoid bidirectional relationships when possible and avoid relationships directly between fact tables wherever possible.
Further reading:
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
10 Replies
You should have a date dimension table with a relationship to the date column on each table that you want to filter on.
If you have multiple date columns in one of your fact tables, how do you expect the filtering to behave? Is one of the columns relevant and the other(s) not? Are you expecting it to filter multiple columns on the same table simultaneously?
Thank you for your suggestion, Alexis!
Indeed, some date columns are not relevant in the specific view I'm trying to create, and, no - I do not expect to filter multiple columns within the same table with the same slicer.
I will try the date dimension table and hope the relationships will work and connect!
Great. If you only need to filter on a single column per table at a time, then the date dimension table is exactly what you need.
I have a similar situation here, if anyone could help, on singe table I have Shipped Date , & Scheduled, I have 2 visuals that shows how many units we "Shipped" per day & how many units we "Scheduled" per day, how to I create a Date Slicer that can filter both visuals?
before hand, Thank you
I'm curious on what the best approach would be for Service Management Data.
We have a table of Issues, which have a Created Date and a Resolved Date.
For a Monthly report of lets say, May,
We would need to filter one chart with all issues Created in May and anothe chart to show all issues Resolved in May,
Both showing issues per day of the month.
Thoughts and ideas?
I found another solution for our CRM power bi tool. where I wanted to see both Open as well as Won opportunities. So to look at all the 'Open' opportunities 'Estimates Close Date' and also know how many opportunities I have in the pipeline on the same page as to also look at all 'Won' by the 'Actual Close Date' on each of the closed opportunities on the same page by using a 'unified column' . Essentially I created a column with a measure that looks at first of all ONLY the estimated close date column to populate the dates, and to check if there is a date in the Actual Close Date column - if there is an Actual Close Date then to use that instead in my unified column. That unified column then gets spun off into its own table with a date hierachy of its own that i can use on the slicer. I use the date hierachy in my visuals and cards and you can see the Open plus Forecast plus estimated contract values unweighted all on the one area chart.
Yes you also need to create a relationship between the unified column and the unified table, but it works a treat and Ive sat with this problem for a couple of years now where I only want ONE slicer on my page, and just this week solved it for my dashboards, SUCH A RELIEF.
