using one date table for multiple dates (For a drill through table)

[DebbieE]

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

[BA_Pete]

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

[DebbieE]

I dont want to do that though as adding date tables for each will create a bigger model

[amitchandak]

@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

[DebbieE]

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.

[DebbieE · Accepted solution]

Ive created a blob post for this which hopefully answers the question

https://debbiesmspowerbiazureblog.home.blog/2020/07/29/power-bi-why-marking-a-date-table-is-a-good-t...

[BA_Pete]

Hi @DebbieE ,

Thanks for sharing, although I found that the solution outlined in your blog appears to be somewhat similar to the answer that I provided.

Pete

[DebbieE]

I wanted to go into a deep dive into all the frustrations I had been having

Are you talking about the role playing dimensions? In this instance it wasnt something I wanted to do  but I added it in for full context along with the reasons why I went for the other option.