Knowledge Sharing: Dynamically Change Dimension on Category Axis

Data Visualizations
1

Hi,

I’d like to share with you a technique that I’ve developed for pitching agains a Qlik solution that showed this feature: You can put a visual on a page, e.g. bars for revenue, and then select from a slicer the dimension from which the categories come for drawing the bars.

Ultimately I came up with a fully dynamic chart area Dynamic Diagrams.pbix (509.4 KB). You can:

  • Change the dimension for the categories from a slicer, e.g. by product, by customer, by salesperson etc. in the same chart. I call this technique Dynamic Category Axis (DCA).
  • Change the number of bars/data-points shown from a slicer. I call this technique Top N Slicer (TNS).
  • Change the measure shown from a slicer, e.g. revenue current period, revenue previous period, YoY variation, etc. with correct data format like currency, percentage, etc. fully functional.
  • Change visualization type between bar chart, column chart, pie chart, line chart etc.
  • Chart title dynamically adjusts to what you actually see
  • Cross-filtering with other charts fully functional
  • Pin to Dashboard fully functional
    DCA1
    DCA11635×914 56.4 KB

    DCA2
    DCA21622×915 58.4 KB

    DCA3
    DCA31634×915 67.9 KB

    DCA4
    DCA41627×911 70.4 KB

    You can play around with the file to experience the effect. Remember: In Power BI Desktop you need to Ctrl-click on the buttons to change the chart type, in the web-browser it’s a normal click. If this is usefull for you, please share the use cases and user feedback. Of course, in a real-life scenario it’s not always usefull to combine all these techniques on the same page. Let me know if you are interested in more detailed steps how to apply each of these techniques.

Thanks to @Melissa for the extended date table!

BR
Martin

7 Likes
2

Great one @Martin :+1:

I first saw a technique for creating dynamic axis over a year ago presented by Robin Abramson (Pragmatic Works), that was based on an Unpivoted Attribute table with a bidirectional relationship to the fact table.

You have chosen another route by creating inactive relationships between your Display- (the dynamic axis) and dimension tables. I will definitely explore that further.

Thanks for sharing it with the Community!

1 Like
3

Thank you, @Melissa !

Yes, intially you have to put all those USERELATIONSHIP into your measures to make my approach work. Then it integrates very naturally with the Power BI concepts. Performance is very well and you keep all functionality like crossfiltering, normal filter pane filtering, and you can combine it with calculation groups. So, once it is set up, using it works as inuitive as using calculation groups.

The file shows plain vanilla the techniques and the steps required. In a customer-facing report you would privide a single set of measures with straight-forward names that check whether the ‘Display’ table is filtered and then activate a relationship or just calculate the base measure.

I’m curious to see where this technique will go!

BR
Martin

1 Like
4

For those of you looking for how-to instructions, Parker Stevens just published a video on Youtube explaining the same technique for switching the categories axis: https://www.youtube.com/watch?v=8e8a3o1w51M

1 Like