Create dynamic X asix

[BInewbie]

Hi All,

I am trying to create a dynamic X axis where by the user should be able to select the variable they want to see on the X axis, with the Y axis fixed to Sales, e.g. Sales by Country, Sales by Person, Sales by Store etc.

The data is all contained in the same table.

I understand I can do this through a 'no dax' method by unpivoting the table for all my chosen vairables, however given my data has 10m+ rows, i dont think this will be feasible.

Any advice would be greatly appreciated.

[Anonymous]

Hey @BInewbie

Give this a try. It will allow you to change your X-Axis via a slicer selection, meanwhile you can put whatever you want in the Y-Axis.

https://www.youtube.com/watch?v=lFd2hjkKz_Q

Hope this helps,

Parker

[mim]

did ou tried bookmarks ?

[BInewbie]

Yes this is a possible work around, however if I have 10 dimensions, I require 10 buttons which will take up a lot of space. Having these in a slicer would be more ideal.

[Anonymous]

@BInewbie

I think what you want to do is create dynamic measures 

sales by store

sales by dimen 2

sales by dimen 3

sales by dimen 6

sales by dimen 4

sales by dimen 5

use the below video to do it, https://www.youtube.com/watch?v=gYbGNeYD4OY.

if you need more clarification after watching the video, please lemme know where you are getting stuck and also post your power bi file 

[BInewbie]

@myayar, not quite as this is adjusting the fact used on the Y axis which is a measure. I want to be able to dynamically change the dimension in the x axis which is categorical, and hence is not a measure.

[Anonymous]

@BInewbie you use the same logic for x axis as well.

switch(true(),

selected value from slicer = sales by store, [Store],

selected value from slicer = sales by dimension 2, [Dimension2],

selected value from slicer = sales by city, [City],

....

)

[BInewbie]

@Anonymous

When im using the switch funciton, it is only allowing me to select measures rather than fields. Any advice?

Similarly in the video, only measures are used.

[Anonymous]

 @BInewbie yeah i just realized that, this actually works in tableau lol, that is why i assumed here as well, my bad.

here is another option https://community.powerbi.com/t5/Desktop/Dynamic-change-in-X-Axis/td-p/86167

[v-lili6-msft]

Hi@ BInewbie

You may try to use Drill mode in a visualization in Power BI

Step 1:

Drag Country , Person , Store into Axis together and sales into Value.

Step 2:

Use Drill mode

Result:

Best Regards,

Lin

[BInewbie]

Thank you, however I should have been more clear - I’m looking for a user to be able to select there chosen dimension through a slicer.

[mim]

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