Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
zdenek_moravec
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

2 REPLIES 2
AllisonKennedy
Super User
Super User

@zdenek_moravec 

 

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: https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

 

Also, I suggest making your date table in Power Query if you can, it will speed up your report: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html


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.
www.excelwithallison.com

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

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors