Calendar Granularity Dynamic Axis

Hi Martin

I'm aware you've directed this question to a specific Trainer which has resulted in delay to your response.

While we wait for that answer, please do confirm in the mean time that you've followed the steps below and that isn't working for you

Create a Date Table: You mentioned that you have a date table called “DimCalendar”. Make sure this table contains columns for Year, Quarter, Month, Week, and Day. These columns should be calculated from the date field.

a Granularity Table: This is your “Calendar” table. It should contain a list of the granularities you want to use (Year, Quarter, Month, Week, Day).

Create a Measure: This measure will switch between the different levels of granularity based on the selection in the slicer. Here’s an example of what this measure could look like:

Selected Measure =
SWITCH (
SELECTEDVALUE ( 'Calendar'[Granularity] ),
"Year", SUM ( 'DimCalendar'[Year] ),
"Quarter", SUM ( 'DimCalendar'[Quarter] ),
"Month", SUM ( 'DimCalendar'[Month] ),
"Week", SUM ( 'DimCalendar'[Week] ),
"Day", SUM ( 'DimCalendar'[Day] ),
BLANK ()
)

Update Your Visual: Use the new measure in your visual instead of the original date field. The visual should now update based on the selected granularity.

Remember, the relationship between the “DimCalendar” and “Calendar” tables does not need to be active for this to work. The SWITCH function in the measure is doing the work of relating the two tables.


Kind regards

Richard


STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector