topic Re: Replacing Quarter label by selected period in X axis Graph in DAX Commands and Tips

Replacing Quarter label by selected period in X axis Graph

Anonymous — Wed, 08 Jun 2022 10:40:54 GMT

Hey team,

I'm trying to do what it seems to be silly but i think it's not

I have a graph showed by Quarter "Q1, Q2,..." and i would like to change it:

instead of showing "Q1" i would like to show the month range for this quarter based on what the user selected in the date slicer.
Example 1: If the user selects the whole year, my bar graph would show 4 bars with the following labels (labels showing the month ranges for each quarter):

But it needs to be dynamic, so..
Example 2: User selected February untill August on the dates slicer - I would like to see on the graph:

Basically i'm trying to group my data and avoid confusion with the users, so they won't think that Q1 contains data for all the Q1 months..

Is that a way to do what i want?

thanks a lot!

Re: Replacing Quarter label by selected period in X axis Graph

OwenAuger — Wed, 08 Jun 2022 14:14:29 GMT

Hey Anonymous

This is an interesting one!

It seems to be one of those problems that's easier to describe than to solve 🙂

I have attached a dummy PBIX with some ideas.

It's late here, but my initial idea is that you would need to modify your data model and measures to get this to work:

Create a special table that contains a row for every possible "partial quarter". Let's call this 'Quarter Extended', screenshot below. It includes columns Quarter, Min Month, Max Month and Label, with Label being "Jan-Feb' etc.
Relate this to your 'Date' table via an intermediate 'Quarter' table containing the Quarters 1 to 4. The relationship between 'Quarter Extended' and 'Quarter' is bidirectional.
Create a helper measure called Display Flag, that return TRUE if the particular Label should be displayed (see below).
Modify existing measures (or use a calculation group) to only display measures when Display Flag = TRUE.
Create visuals using 'Quarter Extended'[Label] on the axis and the modified measures.

Quarter Extended table:

Data Model:

Display Flag measure

Display Flag = VAR QuarterMinMonthSelected = CALCULATE ( MIN ( 'Date'[Month] ), VALUES ( 'Date'[Year-Quarter] ), ALLSELECTED ( 'Date' ) ) VAR QuarterMaxMonthSelected = CALCULATE ( MAX ( 'Date'[Month] ), VALUES ( 'Date'[Year-Quarter] ), ALLSELECTED ( 'Date' ) ) VAR DisplayFlag = CALCULATE ( NOT ISEMPTY ( 'Quarter Extended' ), KEEPFILTERS ( 'Quarter Extended'[Min Month] = QuarterMinMonthSelected ), KEEPFILTERS ( 'Quarter Extended'[Max Month] = QuarterMaxMonthSelected ) ) RETURN DisplayFlag

Having done all this, you can filter on any range of months, and see the appropriate partial quarter labels on the axis of a visual:

This may well need refining, but I think the general idea of the 'Quarter Extended' table is probably the way to go.

Regards,

Owen

Re: Replacing Quarter label by selected period in X axis Graph

Anonymous — Thu, 09 Jun 2022 10:10:12 GMT

thank you Owen for your hard work on this!!

I will try it here 🙂