Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Switch column chart axis by day, week, month, year with buttons

I have a table with a date column and a key figure value.

PBI table.png

I create a column chart with date on X axis and the key figure in values.

PBI chart.png







The request is to switch the X axis from date (represented by day by default) to weeks, months, years granularity with a set of buttons.

After one hour googling, I have created a calender table with columns date, week, month, year

PBI cal table.png

and 4 charts, each with different column name (from the calender table) in the values field

PBI chart2.png

And finally the trick with 4 charts overlayed and 4 buttons, which toggle each chart with the help of bookmarks and selection pane.

But isn't there a easier way to do this? This looks like overkill.

Thank you

Super User
Super User



That's amazing! I love buttons. 


My approach would be to use Drill down in 1 visual. So put Year Month in Axis, Month underneath, Day under that, etc all in the same visual in the correct order. You can probably still set up bookmarks to make this work with buttons, or just train users to use the built in drill down functionality.


UPDATED to add: Use the parallel disconnected arrows for drill down to get the same effect as your separate charts. 


The Microsoft documentation doesn't use a great example, but here's the info on drill down so you can see what built in buttons I mean:


Also, I suggest making your date table in Power Query if you can, it will speed up your report:

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.

Hello Allison

The idea with hierarchy is good, I remember, I tried it, but the disadvantage is, that the hierarchy function groups the same month number, week numbers from all years. So I see month 10 in one bar as sum of months 10.2019 and 10.2020. Therefore I created year-month column additionally to month column to see month 10 in two columns - 10.2019 and 10.2020.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors