Forum Discussion
using one date table for multiple dates (For a drill through table)
I have a star schema in Power BI
My date is marked as date which removes the hierarchy table from the date field and I create my own.
The Active Join is on Received Date
All my measures so far are based on received date
Im aware that each Date in the fact table creates an extra date table to produce the hierarchy. So to save space, you should create inactive joins to the other dates and then remove the dates in the Fact table leaving just the keys. the model should then reduce in size
This works great for the measures. I can create measures based on the none active ones and simply choose to USERELATIONSHIP
So i can have measures for Recieved this Year, Recieved Last year and Closed this year , Closed Last year (For Example)
This is all absolutely spot on. However the issue occurs when the users want to create a drill through (And obviously they do want drill throughs.
This visual was created on the active relatioship so its recieved date
However they want to drill through to the following
I cant think of a way of doing this.
Would it be possible to create some DAX in the Date table to just show a date and then I can name them all in the date table as Received Date, Start Date, Closed Date ?
I cant find any info on this but its the first thing my user tried to do, and Im sure lots of people want to do this. But again, using the correct logic with the date table
Ive created a blob post for this which hopefully answers the question
7 Replies
DebbieE , Not very clear to me. See if Conditionally set the drill through destination can help
https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-2020-feature-summary/#_Cond_dest_drill
I thought I has set this out really clearly, So if its unclear to you it would be good to know why
No this doesnt answer the problem.
Ive created a blob post for this which hopefully answers the question
Hi DebbieE ,
I guess you could duplicate your calendar table for each of the fact dates, for example:
- calendarReceived
- calendarStart
- calendarClosed
or similar, then create active relationships between each of these to the relevant fact table date. You could then use the different calendar dates as each level in the drillthrough.
Not going to save you any model space, but a 'quick & dirty' solution nonetheless?
Pete
I dont want to do that though as adding date tables for each will create a bigger model
