One date slicer for multiple date columns

[Anonymous]

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

[techno]

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?

[C2]

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

[AlexisOlson]

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?

[Anonymous]

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!

[AlexisOlson]

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.

[Anonymous]

Hello Alexis,

Thanks again for your reply. I created the date dimension table and started to create relationships, but... I think I have run into a problem, where existing relationships are preventing the date relationships being active. Quick googling suggests that I might have a multiple fact table problem... and I'm not sure what/how to do - besides totally remodelling everything..?

By any chance would you have an suggestion?

[AlexisOlson · Accepted solution]

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/

https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/

https://www.sqlbi.com/articles/different-options-to-model-many-to-many-relationships-in-power-bi-and...

[Anonymous]

Thank you, Alexis for your reply!

I was kind of fearing it... and hoped that the date dimension table can simply act as a secondary overarching "hidden" filter that would filter the data in the way I need.

Thank you very much for your support!

[Anonymous]

One solution to the problem I might try is to simply delete the 'other' date columns and leave only the one that I will use - so that PowerBI has an easier time to sync them..?