cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jscottNRG Regular Visitor
Regular Visitor

Single date slicer to filter multiple date tables

I'm hoping this isn't actually so difficult, but I've had a tough time trying to figure out the proper setup. I'd like to use a single date slicer to filter multiple tables with dates in them. The tables are like this:

 

roughinspections.pngRoughInspectionsfinalinspections.pngFinalInspections

There is a relationship between the tables like so (using JobID field and the Job_info table):

relationships.pngRelationships

There are no dates in Job_info to use with a slicer.  Using two slicers -- one for the date in RoughInspections and one for the date in FinalInspections -- works in a sense, but because of the implicit relationship between the two, when I select a date range in RoughInspections it filters visuals using data from FinalInspections.  I know this is as designed but it is undesirable in this case because I only want the FinalInspections visuals sliced by the Final Inspection Date slicer.  It seems the logic is such that, as in the screenshot below, when I slice by Rough Inspection Date the Final Inspections count card visual counts only observations that have a related record in the date range in RoughInspections.

 

In short, I have visuals and slicers on the page using data from RoughInspections, FinalInspections and Job_info, below.  I'd like the Job_info slicers (e.g. Job Status) to slice all visuals, and Rough Inspection date slicer to slice RoughInspections visuals and Final Inspection date slicer to slice FinalInspections. (Ideally, this could be done with one date slicer, but I'd be happy with the two behaving independently.)

 

example1.pngun-sliced viewexample2.pngsliced on RoughInspections date

 

Is there a way to do this other than breaking relationships or splitting the visuals across different pages?  Thanks for any help!

Jonathan

1 ACCEPTED SOLUTION

Accepted Solutions
edhans New Contributor
New Contributor

Re: Single date slicer to filter multiple date tables

Shouldn't your slicer be based on the dates in a Date Table where the Date field is related to all of the relevant date fields in your other tables?

18 REPLIES 18
edhans New Contributor
New Contributor

Re: Single date slicer to filter multiple date tables

Shouldn't your slicer be based on the dates in a Date Table where the Date field is related to all of the relevant date fields in your other tables?

otravers Member
Member

Re: Single date slicer to filter multiple date tables

As @edhans suggested, you should have a calendar table (i.e. a dimension table for dates) with relationships with your fact tables, and set up your filters against dimension tables rather than fact tables.

 

Years ago people used to import big pre-created calendar tables (e.g. from a relational database), but if you google around you'll now find plenty of solutions using either M (my preference) or DAX. Here's something to get you started, there's plenty more related content on the web:

http://radacad.com/do-you-need-a-date-dimension

jscottNRG Regular Visitor
Regular Visitor

Re: Single date slicer to filter multiple date tables

Thanks @edhans, do you know of a good resource describing how to do that?

 

I tried setting up a slicer with a Date Table, and added calculated columns to RoughInspections and FinalInspections looking to see if each record was "in range" per the sliced Date Table, but no luck.  I didn't think to set up relations between the Date Table and the other tables, though.

jscottNRG Regular Visitor
Regular Visitor

Re: Single date slicer to filter multiple date tables

Thank you @otravers, I'll give that a look!

edhans New Contributor
New Contributor

Re: Single date slicer to filter multiple date tables


@jscottNRG wrote:

Thank you @otravers, I'll give that a look!


Make sure in the new Power BI Desktop (Since May 2018?) to mark your date table as a date table. When that article was originally written Power BI would figure it out and do it for you. But it is good practice to:

  1. Have a date table in every model that has dates in it.
  2. Mark the table as a date table. Select the table in Table view, then go the Modeling tab, then Calendars, tehn Mark as Date table, then select the Date column.

If it gives you a warning about "built in tables" are removed, that is find. I prefer to control the date table vs letting PBID do whatever it thinks should be done in the background.

Aree Regular Visitor
Regular Visitor

Re: Single date slicer to filter multiple date tables

You would use a Date Table as the main source that all timephased data would be linked too.

 

The typical functions used to create this table is the following DAX
CALENDAR(<start_date>, <end_date>)

https://msdn.microsoft.com/en-us/query-bi/dax/calendar-function-dax

or
CALENDARAUTO([fiscal_year_end_month])
https://msdn.microsoft.com/en-us/query-bi/dax/calendarauto-function-dax

 

Your relationships will look like this

power bi calendar table relationship.PNG

 

This gives you a single source for date slicing.

Hope this helps.

jscottNRG Regular Visitor
Regular Visitor

Re: Single date slicer to filter multiple date tables

@edhans@Aree  thanks again for your suggestions.

 

I created a Date Table and marked it as such on the Modeling tab:

 calendar_table.pngCalendar (Date Table)

 I tried to set up relationships between Calendar and RoughInspections and FinalInspections -- the first one went fine but I got an error on the second:

 

Relationship activation

You can't create a direct active relationship between RoughInspections and Calendar because that would introduce ambiguity between tables Calendar and Job_info...

 

I'm guessing that is causing this problem, that all records are included when the slicer is 'wide-open,' but when I restrict the range even by one day then the visuals go blank:

 

calendar_problem1.png7/1/2016 - 6/30/2018calendar_problem2.png7/2/2016 - 6/30/2018

 

Any thoughts on what I've done wrong?

Aree Regular Visitor
Regular Visitor

Re: Single date slicer to filter multiple date tables

@jscottNRG 

Hey Mate,

 

Can you upload a screen shot of the relationship diagram?

Also the calendar table becomes the focal point for all timephased data (all sources that have transactions by day).
Do not link other none timephased data to the calendar table. I believe this is what might be causing your ambiguity but I would need to see the relationship diagram to be certain. 

jscottNRG Regular Visitor
Regular Visitor

Re: Single date slicer to filter multiple date tables

@Aree

Sorry, I meant to include this in my reply yesterday.  Here is a screenshot of that part of my relationship diagram.  You can see the succesful relationship between Calendar and FinalInspections on the left but the relationship between Calendar and RoughInspections failed.

 

relationships.png