Forum Discussion
Helper IISingle 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?
Resolver IHey 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.
Helper IISorry, 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.
- edhans
Community Champion
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.
- jscottNRG
Here is the relationship between RoughInspections and Job_info. Thanks for the links with more info on correct relationship setup!
- edhans
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.
- Aree
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?
- jscottNRG
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 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!
- edhans
"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. :smileyhappy: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.