Date hierarchy not showing up

Hello,

I have a table with three dates:

My active date relationship is Open Date. The other two HHD Open Dates and Maturity Date are inactive relationships.
E2

I want to use a date hierarchy in both the Open Date and Maturity Date in my visuals but that measurement group is not showing up. Both fields (Open Date and Maturity Date) are configured as date type = Date:

How can I activate the date hierarchy for both the Open Date and Maturity Date?

Hi Rizzotony,

Ideally date hierarchy should be present in Dates table only which is always the best case scenario.
But if you still want to have it in your facts table which I would not advise. Then you can have one to one relationship with your date table. (Which is also a big no no.) I hope this solves your problem. :smile:

Hi @rizzotony,

Yes I agree with @kkrj.ankit here. You don’t need a Date hierarchy in any of your fact tables because the only Date field you’ll ever need in report visuals, slicers and/or the filter pane is located inside your Date Dimension table.

So I recommend you hide all date fields, on the many side of the relationship to your Date table,
in report view. BTW consider this a best practice for all dimensons contained within your model.

It prevents you and anyone else using your model from selecting the wrong field by mistake (the wrong field being every occurance of that field not residing in your Dimension table).

For more on the Date dimension table please see

I hope this was helpful.

1 Like

Thank you. This makes sense.

Is there a resource that speaks to switching dates? I’m looking to create slicers showing the date an investment was opened and when it matures in separate visuals.

My active relationship is open date linked to my fact table. I have an inactive relationship to maturity date, also linked to my fact table. I need to create a table showing maturity date as the active relationship.

Any help is MUCH appreciated.

Tony

Sure, these are a good place to start


.
Or depending on your needs

1 Like

Thank you.