Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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.
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.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
6 |