Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jscottNRG
Helper II
Helper II

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:

 

RoughInspectionsRoughInspectionsFinalInspectionsFinalInspections

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

RelationshipsRelationships

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.)

 

un-sliced viewun-sliced viewsliced on RoughInspections datesliced 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
edhans
Super User
Super User

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

18 REPLIES 18
Aree
Resolver I
Resolver I

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.

@edhans@Aree  thanks again for your suggestions.

 

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

 Calendar (Date Table)Calendar (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:

 

7/1/2016 - 6/30/20187/1/2016 - 6/30/20187/2/2016 - 6/30/20187/2/2016 - 6/30/2018

 

Any thoughts on what I've done wrong?

@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. 

@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

Hey @jscottNRG

 

The relationships that you are showing indicates that

Calendar ->Final Inspection -> Job Info -> Rough Inspection

 

So if you link Rough Inspection to Calendar it already has a relationship via the Job Info -> Final Inspection so you cannot create another active link where an active link already exist.

 

As @edhans would have indicated you can use CROSSFILTER to over come model relationships 

OR

you will need to restructure the data. If you can upload a redacted sample set that would be great.

 

Here is a sample from a file I am working with.

My focal table (fact table) is projects which has associated financial data (dim table) then i have other tables link to calendar. But part of your problem might be related to the fields you are using to from these relationships as well.

In my instance my Financial take is linked to project by an ID but Financial is link to Calendar via Date. 

Are you using the dates to form relationships other than between the Calendar and Final Inspection?

 

sample calendar relationship.PNG

I solved the issue where restricting the date range on the date slicer was causing visuals to go blank.  The problem was that the date fields in RoughInspections and FinalInspections were datetime, and my Calendar table was date only.  I thought I had dropped the time component but had only changed the display format in the Data view.  I had to go back to adjust the query settings like this:  

Transform -> Date OnlyTransform -> Date Only

 

@ @Aree and @edhans were right about the relationship problem causing the deactivated relationship between Calendar and RoughInspections.  The existing relationship path from Calendar ->Final Inspection -> Job Info -> Rough Inspection meant there was a conflict when I tried to activate a relationship between Calendar and RoughInspections.  I fixed it by setting the Cross filter direction for the relationship between RoughInpections and Job_info to "Single."

 

Thanks again to everyone who contributed for the help!

"I solved the issue where restricting the date range on the date slicer was causing visuals to go blank.  The problem was that the date fields in RoughInspections and FinalInspections were datetime, and my Calendar table was date only."

Been there, done that. Smiley Happy

 

FWIW, you can also just make it Date only by clicking on the calendar icon in the field name, and selecting Date. What you did was extract the date. If you just change the type to date (vs Date/Time, or Date/Time/Timezone) it does the same thing.

 

Honestly, I am not sure what the difference is either. But I've learned to look at the little icon.

  • Calendar - date only
  • Calendar with clock - date/time
  • Globe with clock - date/time/timezone.

Glad you got it all sorted. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

I have implemented the Date Dimension. Two tables Investment logs and Customers are linked as highlighted below. The measusures from the  investment logs tables are responding to the slicers but the measures from the customer table are not responding. The data type for the Customers[daatecreated] and Investmentlogs[trandate]

 have been set to Date , dd/mm/yyyy same goes for the Date Dimension. I will need help form anyone as soon as possible.

CustomersTable.PNGRelationship.PNG

Hey Mate,

 

try adjusting the cross filter direction to both. You currently have the cross filter direction to single.

Anonymous
Not applicable

@Aree, thanks , i have made the change but there was not difference. Below are the measures being calculated for display in both tables Total Subscriptions = Calculate( CountRows(qmInvestmentLogs), qmInvestmentLogs[InvType] = "Subscription",qmInvestmentLogs[TranStatus] = "Success") Total Approved Customers = Calculate(CountRows(qmCustomers),qmCustomers[KYCStatusDesc]= "Approved") Total Subscription Amount = Calculate(Sum(qmInvestmentLogs[Amount] ) ,Filter(qmInvestmentLogs,qmInvestmentLogs[InvType] ="Subscription" && qmInvestmentLogs[TranStatus] ="Success") ) Total Redemptions Amount = Calculate(Sum(qmInvestmentLogs[Amount] ) ,Filter(qmInvestmentLogs,qmInvestmentLogs[InvType] ="Redemption" && qmInvestmentLogs[TranStatus] ="Success") ) All work well but the Total Approved customers.

What is the relationship between Job_info and RoughInspections?

There is some other relationship in your model that is causing the Calendar to RoughInspections to be inactive.

 

You can, of course, still use it. You just need to use the USERELATIONSHIPS() function in a filter, but that shouldn't be necessary in this case. usually it is when you have 2 or more relationships between 2 tables and you need to swich which realtionship. See this article for more info on that, but I'd still be interested to know what other relationships you have set up are causing the one you tried to go inactive.

 

Here is an article on how you can create ambiguous relationships that will cause a relationship to go inactive like that as well.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans

Here is the relationship between RoughInspections and Job_info.  Thanks for the links with more info on correct relationship setup!

 

roughinspections to job_info.png

I think the issue you are running into is you have multiple FACT tables (data tables, like sales data, or inspection data) and you are using bi-directional filtering on some of those tables. It will cause relationships to go inactive.

Not the end of the world. Either change the bi-directional crossfiltering settings to single direction and enable the relationship to the date table, or use the USERELATIONSHIP() filter function inside of a CALCULATE() when you need to use it. But I don't think you can do that for a slicer, as slicers depend on relationships and cannot use measures.

 

So maybe turn all bi-directional into single, activate that date relationship, then look at all of your other data and see what broke. If you have some measures that aren't right because of the removeal of bi-directions, you can use the CROSSFILTER() filter function inside of a CALCULATE() function to force that measure to use bi-directional filtering without affecting the entire model's relationships.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

otravers
Community Champion
Community Champion

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

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

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


@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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.