cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
htaunay
Microsoft
Microsoft

Switch X Axis column through a slicer filter

Hi PowerBI experts,

 

I am currently working with a dataset where each line represents information of a specific hour, and would like to plot such data grouped by days, weeks or months, given the users interest.

 

The grouping of the data is achieved by creating custom columns (DAY, WEEK and MONTH) and setting such columns as the X Axis of my plots. However, in this solution I am forced to have 3 different plots for each time slice, when the ideal solution would involve a slicer filter where I could select a time slice and the plot would update automatically.

 

Specifically, and function/trigger that could alter a plot's X Axis through a checkbox would work for me. Is this type of behavior supported by PowerBI?

 

Thanks in advance!

23 REPLIES 23
OwenAuger
Super User
Super User

Hi @htaunay

 

One method is to do someting similar to what @scottsen did here:

http://tinylizard.com/dynamically-changing-chart-axis/

 

In your case, you would need to start with a DateTime table with datetime/day/month/year columns, which you then unpivot, and add a column identifying the granularity. The resulting table (let's call it DateTimeGranularity) provides a many-to-many relationship between DateTime and the values you want on your axis at the different levels of granularity.

 

You also need to add a column to enforce sort order.

 

In Power BI, you can then create a bi-directional relationship between the DateTimeGranularity table and the DateTime table, so that filtering on the Time Granularity column (DateTime, Date etc) also filters the DateTime table.

 

I've uploaded a dummy pbix file illustrating this which should make it clearer:

https://www.dropbox.com/s/aoflh2unznifyac/DateTime%20Granularity%20Selection.pbix?dl=1

 

The data model looks like this:

 

Capture.PNG

Note: you can also use drilldown on visualizations if you have a hierarchy of axis labels - I added another tab in the file illustrating this.

 

Owen 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn
Anonymous
Not applicable

Excellent solution @OwenAuger, thanks! Just a question, how do you use the order column in your visuals afterwards?

Hi @Anonymous

 

Are you asking about the Time Value Order column in my sample pbix file?

 

That column is used as the sort-by column for Time Value Text. Since Time Value Text contains a mixture of granularities (Date, DateTime, Month and Year in my case), it is impossible to sort this column based on its own values.

 

So Time Value Order is not directly used in any visuals, but set as the sort-by column for Time Value Text in Modeling = > Sort by Column. I also hid it in report view as the end user has no use for it.

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn
Anonymous
Not applicable

It's the Modeling -> Sort by column, thanks!

@OwenAuger Thanks for this idea it works very well except in the situation I ran into. you can see my post below. thanks for this !

Try using multiple charts that you selectively show/hide with bookmarks and selection pane and use buttons to switch between them. I use this method extensively.

 

So you woudl have 5 version of the chart and create bookmarks with only one of these shown. Then create buttons linked to those bookmarks. (tip when saving bookmark be sure to uncheck the data propery of the bookmark so it only remembers which visuals to display and does not reset any filters. 

 

 

no, because the next step is to include charts with dynamic labels if that make sense. say breakdown by products on the label or by regions on the labels. so then it would be 5 X the number of breakdowns. so just for those two I mentioned I would need 10 charts....

 

5 for the dates X 2 diferent types ( I am planning more than 2.... probably 8 different mix types..

 

Why not just teach people how to use drill down capability of the charts and matricies to switch what is displayed? 

 

 

 

again, no that is not going to work. The users want to use buttons. I know this is possible for sure but I am unsure how to approach this and I have literally been banging my head against the wall all day 😞 

Hi All, I solved the process and it works just fine now.  I will have to make a dummy data set later so other people can use the technique that I came up with. 

 

 

That woudl be AWSOME!! there are several fairly robust sample data models out there - https://docs.microsoft.com/en-us/power-bi/sample-datasets

 

I will check that out, I am also working on dynamic labeling right now-- didnt see much out there.... but maybe its not possible?

Can do dynamic tiles and text boxes but I know of no way to use DAX in a lablel name or any other way to make it dynamic.  I'm sure there is an idea out there.. IF not there should be - if you create one let me know and I'll vote for it!

my bad -- its not dynamic labelings its using dynamic legend. so one graph could esentially replace many graphs if you get my drift. It would allow for massive reuse.

 

Ok, I'll monitor this thread so if you figure something out please post as the only way I have done this is  a bunch of charts and bookmarks.  Honestly even making 20 copies of a chart and buttons to select which one is pretty quick and easy though not elegent it works and is the only way I have yet figured out.  Could do the bookmarks to switch the X axis and then use multiple tabs to handl the by Area, Product etc... 

 

so, almost there I have everything working except quarters. Does anyone know the proper way to use the M(Power Query Lanagugage) to do something like this equivilant in excel/PBI Measures ? 

 

Quarter 

DateFormat = year('Quarter Dates'[DateValue])&"-Q"&ROUNDUP(MONTH('Quarter Dates'[DateValue])/3,0)

 

Month

Dateformat =year('Quarter Dates'[DateValue])&"-"&MONTH('month Dates'[DateValue])

 

I have not been able to find this out yet.

 

ok-- I figured it out, in case others need this!!

 

= Table.AddColumn(#"Renamed Columns", "Custom.1", each Text.From(Date.Year([Measure]))&"-"&Text.From(Date.Month([Measure]))) 

 

BOOM DONE.

 

I will keep you updated on final solution.

Date.QuarterOfYear([Registration Date]), Int64.Type)

thanks for the input though I appreciate it. I would normally agree with you but this will become a maintnance nightmare when they are going to make adjustments to these so then I would need to do that X 20 make sense?

 

another idea I had was creating dynamic labeling if that was possible. this then would cut it to a 5 charts regardless ( just for the date options)

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.