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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.