Switch X Axis column through a slicer filter

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 :)