Forum Discussion
Filtering multiple Dates across multiple queries with an existing relationship
I have notebook where I have 2 different Data Sets and I already have a relationship exsiting on a particular value but I would like to create a date slicer that allows me to cover 2 different date fields on the different queries.
8 Replies
If I understood correctly, you need to create a calendar table and link your tables to that calendar table and use date from calendar table as slicer.
You can easily create calendar table using "M" query in PowerBI Query Editor, let me know if you need help with that.
Thanks,
P
lets explain little bit clear dude .
Do u want link the existing table with already exist column ?
or
do u want to create on intermediate table called date master , from there do u want control these two tables ?
Hi WILSONB,
I agree with parry2k, creating an individual Date table should be a possible solution in this scenario.
In addition to use Power Query(M) to create the Date table in Query Editor, you can also use CALENDAR or CALENDARAUTO Function (DAX) to create the Date table in Data Model.
And for more information about how to create and manage relationships in Power BI Desktop, please refer to this article.:smileyhappy:
Regards
Hey All,
I work with WILSONB and I wanted to help clear up the ask.
We already have an existing relationship between the two tables that is essential to the report. We tried to create a calendar table and create that relationship, however this existing relationship would not allow it. We were wondering if there was any other way to work around this.
There are dates in both sets and we just want to make sure that everyone is looking at the same range within both tables. The other option for us was to create filters on both data sets for a moving date range, but we would prefer it to remain dynamic.
Does that make sense?
Cool,
1. Create on Date Master table.
Example : Use this code create Table in Dax :
calender = CALENDAR(DATE(2016,1,1),DATE(2016,12,31))
It will give u new table from 1-jan-2016 to 31-12-2016
2. Link the Date master with yout two table . (Miles Data and Accident Data),
Before that u have to Change the bi-Direction filter to single direction on already existing table connection, don't forgot it.
Then use date slicer from date master.
Try this if it is not helps u let me know i will help u
Not sure if it is clear. May be you need to provide some more informaton. This is what I understood:
- Mile Data is linked to Cost Center table on cost center field
- Accident Data is linked to Cost Center table on Service Center (i believe)??
Miles and Accident are not related to each other what I see from diagram, is this correct understanding?
Were you able to figure this one out yet? I am trying to create a date slicer to filter across three different tables.
Hi,
The Date slicer should come from your Calendar Table. The date column in each of your data tables shoule bear a relationship with the Date column in your calendar table.
