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!

Hi htaunay One method is to do someting similar to what Anonymous 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: 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 :)

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

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

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

Switch X Axis column through a slicer filter

23 Replies

OwenAuger
Super User
10 years ago
Hi htaunay

One method is to do someting similar to what Anonymous 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:

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 :)
- efigoldman
  Frequent Visitor
  9 years ago
  Great post.
  Are you sure there is no other way? like we used to do with MDX? by replacing sets?
  - vanessa
    Post Patron
    9 years ago
    Any work around for this, using DAX with SWITCH?
- mws5872
  Helper II
  8 years ago
  this works great except in cases where you add weekend date and quarter and the dates correspond with a month end for example.
  
  Then you are not allowed to sort by the column because it does not have a unique value. any ideas?
  
  the Datekey is joined to the date table but the time value order is what is being used to sort. you see it will not work as there is not a unique value.
  
  Attribute DateControl DateKey Value Time Value Order
  Month 1 8/1/2021 0:00 8/1/2021 5137
  WeekEnd 0 8/1/2021 0:00 8/1/2021 4935
- mws5872
  Helper II
  8 years ago
  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 !
  - Seward12533
    Solution Sage
    8 years ago
    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.