Dynamic Y axis based on multiple variables - Need Help!

[Anonymous]

Hi All,

I have a dataset with 6 columns - ID, X1, X2, Y1, Y2, Y3. X1 and X2 variables are categorical, having 3 and 5 categories respectively. Y1, Y2 and Y3 variables are continuous. Y1 and Y2 are ratio variables between 0% to 100%. Y3 denotes whole integer (>0).

I want to create a 'Line and Clustered Column Chart' where the Column chart will be based on Y3 and will be fixed, the line diagram will be based on Y1 and Y2 and needs to be dynamic, i.e. the user can change between Y1 and Y2 by using a slicer / dropdown. The X-axis also needs to be dynamic. I got the solution of dynamic X axis from other threads in this community, but could not find any post on how to make the Y axis dynamic as per the requirement.

Please help!

Regards,

Sagnik

[Jihwan_Kim]

Hi, @Anonymous 

Please kindly check the below picture and the sample pbix file (link down below), if it is what you are looking for.

I used Tabular Editor in Power BI, in order to select measures in slicers.

If it is OK with you, please share your sample pbix file, then I can try to come up with a more desirable solution.

https://www.dropbox.com/s/x3x9qbw28bgq0fn/ms%20power%20bi.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Hi Jihwan,

Thank you for the solution. This is exactly what I was looking for. Could you please tell me how did you create the datasets - 'Y1&Y2' and 'Y3' and how the measures are defined within each dataset? It will be of great help if you can show me the stepwise process. 

Regards,

[Jihwan_Kim]

email address: data@flowanalysis.co

[Jihwan_Kim]

Hi, @Anonymous 

May I ask if Tabular Editor External Tools for Power BI is installed in your power bi desktop?

Please send me an online meeting request ( teams or google meeting etc...) then I can share my steps.  

Terribly sorry to say that it is quite difficult for me to write every single step.

I will send my email address to you via the message.

Thank you.

[Anonymous]

Sorry, I do not have tabular editor external tools installed. Is there any alternate way to create the same measures within power BI itself?

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Picture1:  I used the original table structure.

Picture2: I unpivoted the original table structrue.

unpivoting the original table structure gives the choice to select X1 or X2 by the slicer.

the link to the sample pbix file is down below.

https://www.dropbox.com/s/dzpzv8u879y02wh/ms%20power%20bi%20V2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Thank you very much! I'm able to successfully incorporated your solution into my data.

Regards,

[Jihwan_Kim]

Oh.. Ok,

I will try to do it.

I will take a few more minutes. please wait.

[amitchandak]

@Anonymous , One of the way is to use buttons and bookmarks when you want to change axis

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

or

Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/

For measure

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern