Help with USERELATIONSHIP on Slicers

[vadnaisc]

Hello,

I am working with 2 tables Date and FactReservation have an active relationship between 'Fact Reservation'[StartDate] and 'Date[date]' as well as inactive reservations with 'Fact Reservation'[EndDate] and 'Fact Reservation'[BookDate].

I also have duplicated the FactReservation table, removed all columns except for StartDate, EndDate, BookDate and ResID, and unpivoted this table, leaving me with a ResID column, Attribute (Date Tye) column and Value(Dates) column and named this table  Reservation Dates. Reservation Dates and FactReservation are connected with ResID.

My end goal is to have slicer where I use Date[date] and can chose which date type I want to filter by and filter a table with this. The DateType column will then reflect the selection of either book date, start date or end date, and the date column will reflect the date value for that row and type. The slicer I have below is using 'Reservation Dates'[Date] and 'Reservation Dates'[Date Type] and is working to filter my table, however in order to use this across other reports and pages, I need to use the Dates table instead so I need a way to turn on the 2 inactive relationships.

Because I do not want to CALCULATE anything, I have not been able to make a measure with USERELATIONSHIP that does anything to help me. 

Is there any way to accomplish this? 

[Anonymous · Accepted solution]

You can't achieve this with the current setup and the constraints you place on the design. To realy do it you have to have 1 fact table where every row is present 3 times and you add additional 2 columns where 1 will hold the type of date and the second the very date. Then you create an auxiliary table with the names of the 3 types of dates (connecting it as usual to the fact table on the auxiliary date name/type field) and connect your Date[date] to the new date column added to your fact table. If you then put a filter on the new date type column (Book, End, Start) and at the same time on the Date table... you'll get what you want without a measure. Please remember that you should never use fact table colums on the canvas (only the dimensions') unless the dimension is degenerate.