Hi guys,
Seeking some guidance if you have ideas, please?
I have two tables – “Open” and “Closed” – both have dates and country fields. They relationship was absolutely fine with Calendar table. But not with the dimCountry table. Says it would jeopardize the relationship between the dates and these two table. How could I bring together the Country info so that it would work as a slicer on both these tables in visuals? Any advice?
Thank you.
Sounds like it should be doable, but impossible for me to offer any specific advice without seeing your data and data model. Can you please post your PBIX and the underlying data in Excel or CSV (I strongly suspect the solution here may involve some Power Query transformations of your data model)? If it contains any sensitive data, here are some quick techniques for anonymizing it so that you can post it.
Thanks!
Hey BrianJ,
Thank you so much for having a look. I have deleted a couple other not relevant queries and renamed a few few values and fields, but it is pretty much the same model. I just cant figure out how to get both tables to filter on the country in visuals…
Any ideas or advice are very welcome. 
Sample_Slicer_issue.pbix (1.1 MB)
Okay, super easy fix on this one. If you rearrange your data model view into the cascading arrangement that @sam.mckay recommends, where the filtering dimensions flow “downstream” to the fact tables, the problem becomes immediately apparent - the country dimension table only has a relationship to one of the two fact tables:
Just connect it to the other table, and the slicer now filters both tables perfectly:
I hope this is helpful. Full solution file attached below.
I just check this same in my model, it did work absolutely fine. Even feel silly…
The reason why it was connected to only one table - its that it wouldn’t allow me to connect to the Closed table. Kept getting a message it would jeopardise the date table relationship.
Either way, i am extremely happy it does work now! Thanks a lot for having a look. 
Have a nice weekend.
Don’t feel silly - It wasn’t obvious to me either until I rearranged the data model. Sometimes, just having another set of eyes on the problem is all that’'s needed.
One other thing I meant to mention - I don’t think you need that second date table. You can just connect your primary date table to the Period of Last Update field using an inactive relationship, and activate it with USERELATIONSHIP whenever you need to switch from the primary (active) relationship. This will let you delete the Last Update Dates table and simplify your data model.
Brian, that is a great tip. I’ll be honest with you. I couldn’t work out the DATEDiff with USERELATIONSHIP, to calculate how long its been since last update. that’s why i created the extra date table. Maybe you know how i could make that work? : )
Which two dates are you trying to calculate the difference between?
BTW - if you want to further simplify your model, denormalize your country table by merging your DIm Skill VDN table with it, and then you can delete the Dim Skill VDN table.
Definitely gonna do that - will merge and delete the other table. Didn’t think about that. Great advice!!
Datediff i would need from today, as that Table would be refreshed daily. So something like this: datediff(Open[last update], today(), day) and to exclude weekends.
thank you so much for your help with this.
Give this a go as a calculated column on your Open Cases table:
Business Days BTW =
CALCULATE(
COUNTROWS( Dates ),
DATESBETWEEN(
Dates[Date],
'Cases Open final'[Period of last update - Date],
TODAY()
),
Dates[IsWeekday] = "TRUE",
USERELATIONSHIP( 'Cases Open final'[Period of last update - Date], Dates[Date] )
)
Revised solution file below.
Brian, that absolutely works. Deleted the other dates table Thank you so so much for all your help with this one. Made such a difference! Have a lovely Sunday.