3+ approaches for Dynamic Axis in Power BI

There are different approaches to having a dynamic axis in Power BI. Adam walks you thought 3 way to accomplish it within your Power BI report.
Patrick's Dynamic Axis video: • Dynamically change cha...
Kasper's Blog: www.kasperonbi.com/dynamicall...
Patrick's Personalize Visual video: • A different perspectiv...
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #DynamicAxis #GuyInACube

[@viveksadashivaiah403]

Didn’t know about the personalized views on Service!! I’m so looking forward to take our organization‘s reporting to the next level!! Thanks Adam 👍🏽

[@javiergonzales6118]

Wow. This is just what I was looking for. Didn't know about personalised visual or perspectives. Thanks for sharing with the community, Adam

[@yelperalp]

Don't forget drill up + drill down!! Much easier...

[@alexanderchernov6792]

Awesome 8 minutes in a cube. Just seen Personalized views for the first time.

[@emmawith8473]

I use the first approach all the time, I find it works very well so long as I keep all my bookmarks clearly named and well-organised! Users seems to quickly understand how to navigate this set-up too.

[@rvweteling]

Hi Adam, thanks for the options. I wanted to tackle this about a year back where people could switch between month or week on the axis. And this would reoccur on each page in the report. So I did something like option 2 but not exactly as I didn't need 'special' measures. I created an expanded calendar-like table, where each date is in twice, looking something like below: Date | View | Label 1-1-21 | Month | Jan 1-1-21 | Week | WK01 it had some other columns as well to identify if it was the previous month (or week), as I always wanted to show the last 6 months or weeks. I connected it to my regular calendar table. I put the Label on the x-axis, can slice on 'View' and all my data is connected to the regular date table and I can use measures without identifying what 'view' is selected (like: measure = IF(SELECTEDVALUE([View])="Month", xxxx, yyyyy)) as this table will group and filter the results. It was some work to set up the table, but then I could use this on the different pages (it's a big report) and with syncing slicers, all the pages have the same view. Afterwards even expanded it to switch between 'past 6 periods' and 'past 5 + current period' (duplicated lines again, and had different DateDif results so I can show DateDif 0-5 always).

[@lukeminikoski]

All new features are incredible, but my end users just want tables and matrix to export to excel! HAHAHAAH

[@ThePPhilo]

Dataflows for Excel has been promised recently which will solve this. We have the same issue.

[@nouamanebrinssi6996]

many many thanks for this tip

[@cognovo]

Hi Adam, thanks for the amazing show as always! I have a presentational question: How do you zoom into parts of the UI and draw blue squares etc. when presenting the UI. Is there any kind of presentation software to be able to do that ? Just curious...

[@massimocolaceci]

Great! I didn't know options 3/3+

[@noorjannah5112]

Hello! can you please make a video about stock management for supply chain. How to keep freshness of stock by expiry date and also by batch? Really appreciate your help!

[@davelaws]

On approach #1, if I had the whole report change would the button would that affect the performance of the report due to too many visuals?

[@mohammedsihadjmohand8326]

what if I need to do it on both Legend and X Axis? Swapping between Dimensions

[@tawerwerroronoa3825]

hi.. it is possible in excel to dynamically select items to show in one chart and can also do multiple selection with the help of a little vba.. is it also possible in power bi? thanks in advance

[@AJM2183]

I don't think I can get tabular editor with my work, is it essential for approach 3 and 3.1?

[@joshholt2877]

Can you make a video on how you would handle this for a dynamic hierarchy?

[@premjitchowdhury262]

Taking the dax approach

[@abcthegreat1]

Is approach 3 available in the Report Server?

[@AnilKumar-eb8hc]

Hello, I tried as per the process shared by you, but graph are not changing and show blank. Could you please help me.

[@VladMZ]

This specific example with time dimension is a lot easier to solve with having all three levels in the axis and teach people how to drill down and up. Instead of messing with the perspectives.

[@user-gs1eq4ho9e]

i am using the table switch method, but how can i sort the x-axis based on the time sequence? In you example, the Month is randomly sorted. i have a Month list (Jan'24, Feb'24, Mar'24..) and Quarter list (Q1'24, Q2'24..., Q2'25...), how can this be sorted based on time sequence?

[@paulnandes2841]

i'd love to see a video showing how to deal with data snapshot comparisons. For the use case of pipeline velocity for instance, I have every week an export of deals from a sales CRM and I wanted to compare those snaptshots to show pipeline velocity , how fast we are moving those deals from one stage to another, etc. I'm already ingesting and keeping 4 snapshots with a column that mentions the snapshot version/date. but then? thanks so much!

[@AchieversDataEngineering]

Hi Adam, I have a situation where I need to create a bar chart in power BI having a value selector ( I have 2 values) and a slicer where through which I can select Top performer (as Top 5) or All values. As of now I have un-pivoted the table and able to achieve a value switch slicer for bar chart. I have tried many tricks for to create a slicer or button for All vs Top performer but nothing seems to be working for same bar chart. Any help would be appreciated .

[@ashokreddy3300]

Let's say you want to find top5 products on the basis of sales. Find first top 5 products and then others ,if " others" value is not in your product name column ,add that value i.e create table and give value as others and now you combine using union function the product name table and this you get a new table of single column with all the products and others value so now you can place product names and others(other than top 5). Now write measure for top5products and others .then u need not to go for slicer.

[@alocalthreat]

Less than 10 seconds in and I'm already contemplating clicking on the next video because Adam said "axises" LOL! Just kidding guys, great vid.

[@akhilannan]

Personalize visual is amazing. Only drawback is it doesn't show the changed visual when u export report to PPT/PDF.

[@peterhoogendoorn6796]

Nice options! Is there also a way to alter the Y-axis dynamically? In my bubble chart, the top and bottom bubbles always get cut off midway. My end-users complain about this so would love to see if there is also a way around that as well! Thanks in advance.

[@ThePPhilo]

Will Thompson did a presentation a couple of years ago that suggested everything in a visual would be controllable via DAX, never fully came to fruition unfortunately. We were hoping for dynamic constant lines 😟 Only workaround I have found is 1980's style layer visibility and bookmarks where you create multiple versions of visuals and hide them based on button clicks like in Adams video.

[@antonycatella5901]

I spent ages investigating using Approach 2 to dynamically change both the x- and y- axes in the same visual. I could either make y-axis dynamic or the x-axis dynamic. As ThePPhilo mentioned I had to use bookmarks for the functionality I wanted

[@DarkOri3nt]

I was going to say personalised visuals would achieve this. Just be aware if you export to power point the personalised view isn't saved as of this recording last I checked or working with Google present.

Why don't you just place Year, quarter and month from the calendar in the x Axis to let the user drill-down with the buttons of the visuals?

[@TomShark-tz5cy]

Approach 2 makes impossible a 'drill through option' because it creates a virtual dimensions.

[@Baldur1005]

Personalize Visual require some work/finetuning from Microsoft side - for end user full model/all visualizations are too much. I know you can limit it with perspectives, but it is still not something is very good.

[@vyazici]

I prefer #2 but when you need to apply the dimension as a filter it doesn't work. Shame

[@ThePPhilo]

Is there a technical reason why DAX can't be used to dynamically change a legend? would be so much more powerful, it's how all the other BI tools implement this functionality. Users could customise multiple visuals from a single slicer selection.

[@antonycatella5901]

Hi there, are you trying to change the Legend values dynamically or the name of the Legend? If it is the former I do just that with using the dynamic axis approach decscribed in Approach 2 but use the Dimension values in the Legend field well and use the Dimension Name in a slicer

[@ThePPhilo]

@@antonycatella5901 Hi, it would be a game-changer to be able to dynamically select (using DAX) columns to use in the axis, legend and values fields for visuals. Report designers would have complete control (unlike personalised visuals) and users could switch multiple visuals from a single slicer. I'm not sure if there is a technical issue in the way PowerBI works that prevents this from being implemented, it's how all the other major BI tools do it - A switch statement in a formula-driven by a user selection. No need to bloat your model or reprocess it every time you want to explore a new attribute.

[@antonycatella5901]

@@ThePPhilo it would be brilliant to have that functionality. I think I need to ask that question on Guy in a Cube saturday live to see if there is a reason for it. I have not used any other BI tool so cannot comment on them. Personalised visuals are fine for simple measures, where changing the aggregation does not matter unlike measures using complex logic / reliant on disconnected tables etc.

[@Mim_BI]

the Problem is calculated column in DAX are static, they change only at runtime, but who knows !!! according to one of the PM, it is coming :)

[@ThePPhilo]

@@antonycatella5901 I'll tune in to see what they say :) This is how Tableau allows users to chose which fields to use in columns and rows of a matrix, so much better than personalised visuals IMO - help.tableau.com/current/pro/desktop/en-us/changing-views-using-parameters.htm Would be great to know if it's a technical/architecture limitation in PowerBI or just something they have chosen not to implement.