calendar granularity dynamic axi

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Calendar Granularity Dynamic Axis

Calendar Granularity Dynamic Axis

· Medium Priority · Version 365

Calendar Granularity Dynamic Axis

Hello again Jens,

I have emailed you some screenshots for this.
I am trying to replicate a Tile Slicer of Date granularity for a dynamic date axis.

I have used one previously built by others, and have tried copying the same format as well as using online guides. However, the only thing it changes on the axis in question is the name (ie. Year, Month, Week, Day), and doesn't actually change the axis to those formats.

My date granularity table is called "Calendar".

I have sent the data model - I have tried both with and without connecting a date relationship between DimCalendar (main date table) and Calendar (granularity selector).

Are you able to spot what I'm doing wrong?

Thanks,

Martin

RE: 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

RE: Calendar Granularity Dynamic Axis

Thank you Richard, happy for anyone to help.

1. I hadn't created the measure, silly me.

2. After creating the measure, it's not letting me place it in the x-axis of the visual or in the slicer.

I have used the same fields in the measure as in the granularity table. CalendarKey is a date field. Do I need to change them? Thanks for your help.

Measure:

Date Slicer =
SWITCH(
SELECTEDVALUE ( 'Calendar'[Calendar] ),
"Year", SUM ( 'dimView Calendar'[Year] ),
"Month", SUM ( 'dimView Calendar'[MonthCode] ),
"Week", SUM ( 'dimView Calendar'[WeekCode]),
"Day", SUM ( 'dimView Calendar'[CalendarKey] ),
BLANK ()
)

RE: Calendar Granularity Dynamic Axis

Hi Martin,

Apologises for the delay while we discussed this internally.

Jens has shared from context with me that you would like to have a similar view to another report created by a colleague.

Can I check that the CalendarKey formatting. It might not be working correctly if there is an issue with the date format?

Can you confirm if that data is working correctly in the correct date formatting in another visual?




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

RE: Calendar Granularity Dynamic Axis

Thanks Richard - it is definitely in Date data type, and long date format.
The icon in the field list is Identity field* rather than date hierarchy.
In other visuals it works correctly - but I always have it by day. I can't select month or year from a hierarchy as I can with other date fields. Is this a problem?











*(Fields with this icon are unique fields, set to show all values, even if they have duplicates. For example, your data might have records for two different people named 'Robin Smith', and each is treated as unique.)

RE: Calendar Granularity Dynamic Axis

Hi Martin

Appreciate that update, very odd. I will discuss with the Power Bi Team here and see if we can formulate an answer to this question


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

 

Training courses

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Power BI tip:

Create Interactive Dashboards

Build interactive dashboards by leveraging features like slicers, drill-through, and bookmarks. Slicers allow users to filter data dynamically, while drill-through enables detailed exploration of specific data points. Bookmarks help you save the current view, making it easy to switch between different states of your report.

View all Power BI hints and tips


Server loaded in 0.08 secs.