Dynamically change chart axis in Power BI

In this video, Patrick builds on a blog post from Scott Senkeresty to dynamically change the axis of your visual within Power BI. This uses a combination of Power Query and DAX.
Chris Cook - @Finalyst89
Scott Senkeresty - @ScottSenkeresty
Dynamically changing chart axis
tinylizard.com/dynamically-cha...
LET'S CONNECT!
Guy in a Cube
-- guyinacube.com
-- / guyinacube
-- / guyinacube
-- Snapchat - guyinacube
-- / guyinacube
**Gear**
Check out my Tools page - guyinacube.com/tools/

[@juancpgo]

I think it would be more useful if you first showed in the videos the final result, so we actually make sense of what it's all about, and then show how you did it.

[@ringovski1980]

It's really hard to follow what he is doing because he is switching the camera so often.

[@josetrejo3200]

I agree

[@culpritdesign]

This is great! I was trying to do some kind of calculated field based on a parameters and it was not going well... now you make me realize I was essentially trying to program the unpivot function...

[@hermesjrd]

Patrick a Big Shout-Out to You. This Tip is awesome.

[@calito44]

Man!!!!!! love it the simplicity!!!! Usually solutions like this one involve some tweakys and geekys around the DAX but this one I fetl it simple and straightforward I will adopt it... thks!!!!

[@Crack3044]

Great video! I have a new challenge for you that builds on top of this dynamic axis solution. I have four data attributes: Cost per Thousand, Cost per Click, Click Through Rate, and Unique Click Through Rate. The first two are dollar figures and the last two are percentages. My issue is that when a user selects any given column, I need the value on the y-axis to change its type. For example, when the user selects Cost per Thousands the y-axis should display dollar amounts. When the user selects Click Through Rate the y-axis should display percentages. I've been banging my head against a wall for a couple of hours now. Keep up the great work!

[@kingingway3308]

Great! Was looking for something like this!! Thank you, Patrick

[@GuyInACube]

Awesome! Glad it helped!

[@marcosoliveira8731]

Still so relevant in 2019. Tks a lot!

[@GuyInACube]

Most welcome. Agreed, still a thing 👊

[@lauroteixeira8507]

The best and simplest solution! Thanks man !!

[@cristian.angyal]

Great video Patrick. Thanks for sharing it! One small comment though: Marco is also saying that Measures should be written without the Table Name in front as a "Best Practice" :)

[@joshblackman2380]

Mind blown. I love the crossfilter. I was just trying to do this in a report, many thanks.

[@sabasultana7804]

If nothing is selected then I want to show color values not product. How to do that ? @patrick

[@bernatduranmascorda7171]

This is GOLD! Thank you, I'm a huge fan of your channel!

[@GuyInACube]

That's so awesome to hear. Thanks for watching!👊

[@pankajupadhyay5356]

Wonder full , Very Useful , i am searching for this solution from almost 1 week

[@davidcarballasreboredo]

nice tip Patrick!

[@sophiahennouni3276]

Yet another amazing trick from Patrick :) Thanks!!

[@ektaatomar]

I was looking for this solution. Thank you.

[@GuyInACube]

Awesome! Thanks for watching 👊

[@kennynguyennz]

Awesome, thanks Patrick.

[@GuyInACube]

Most welcome! 👊

[@labbylabby7348]

Superb ! Such a easy to learn with your tutorials and method .. thank you

[@MarkCBB86]

Very clever - Thank you for sharing

[@AISHWAR04]

Hi Patrick Guy in a Cube channel is excellent. I always turn onto you guys if need some help.Well, we also had the same requirement for our client and we had used the same logic of Unpivot around 6 Months back.

[@carolina-mi2jd]

Wlove it!! It worked for my calculated variable, thanks a lot!!!

[@learnspreadsheets]

Great video! I sometimes get issues with bidirectional filters too, what can I read explaining why they break sometimes?

[@jasonzhang6534]

I like the explanation, very clear. It really made my life easier:)

[@seongjho8492]

Wow. this is wonderful, sure would've helped me to save a lot of time a few months ago, I was doing this all by using SQL query unions. We were doing something similar to this where we wanted the x axis to be either days or weeks and a filter would decide either or and it also had several other filters that it needed to be unpivoted against. it took some fiddling to get the charts to show near instant changes with nearly 3million rows in the fact table but it was worth the effort in the end.

[@bazllaburke]

you dudes are awesome! you make me look so good at work!

[@npaler01]

Patrick, you slipped in there a bi-directional cross-filtering golden nugget!! Great stuff

[@AW-pp4jg]

Thank you for all the videos; they are fantastic. Is there an alternative/better way of doing this if you have a large data set?

[@TristanMalherbe]

Nice technic ! I used a similar pattern to put on the same X-axis fields from the same table but different grain. For example you want to see on a bar chart sales by Continent & country: Europe, Asia... but also France, China and Japan (on the same X-Axis, no drill-down). So the data for Europe and Asia has to include all the sales in their respective continent minus the sales coming from the country also displayed on the graph (so here: Europe = Total Europe - France and Asia= Total Asia - China - Japan). And, as required, the countries which are included on the X-axis are dynamic and automatically deduct the total of their original continent. I'd love to share this technic with you :) Also combines some M & DAX !

[@GuyInACube]

Thanks for watching! Follow me on Twitter (@patrickdba) and we can start the conversation.

[@Bzuhl]

Oh my god that's what I needed to beat the sql procedural reports and push forward with Azure AS and DAX. Here goes my evening I guess...

[@ravikumarvishwakarma8948]

Too cool trick, you are genius !

[@ben_habib]

"I went to my favourite search engine... Bing" - I laughed way to hard at this

[@joaquimcosta952]

Amazing solution!!!! Thank man....

[@dogsborodave]

Very clever! Like this.

[@braiice]

Boom- huge thanks guys

[@GuyInACube]

Most welcome!

[@raulvinuesaarjona2621]

Very useful solution Patrick

[@GuyInACube]

Thanks Raul!

[@satishkurra7688]

What if we have dimensions coming from different tables instead of single table

[@Markfriedman59]

Hey Patrick. Great videos! But a question on this one... if my source table is refreshed daily from sql server, and my values originate from this table, how do I propagate those changes to the duplicated table and pivoted table from the updated data? Reference table? Thanks!

[@manuprakash1989]

awesome dude..this is what I'm looking for..many thanks !! advance new year wishes

[@GuyInACube]

Excellent! Happy to hear. Happy New Year to you!

[@sammertalib8898]

Hi! Amazing video, is there a way to change axis with calculated columns? Tried it but sadly unable to do so. Any suggestions? Thanks!

[@KawanExcel]

you saved my day .. Its Awesome

[@jimalyajenkins9133]

This is sick!

[@divyaahuja9951]

Hey thanks for this, but just wanted to check if we can do similar thing for KPIs , I mean Y-Axis?

[@chetana2166]

That's great Patrick. I have a question. Do we need to create unpivoting table whenever new data comes in to the main table?

[@khadarvali3043]

Hi Patrick, Nice video on dynamically changing axis.I am have watched all the videos has published by you.thanks for sharing your knowledge to us. I have one concern in this video,We are duplicating the same data in model for creating one slicer. I am not feeling that this is optimal solution. sorry for saying not. do we have any other option to get same solutions?

[@brianblakeley7069]

What about making both plots and use bookmarks to toggle between them? That is what i tend to do. I enjoy your videos. Keep sharing ideas!

[@anhnguyenduy6861]

Bookmarks doesn't work in dashboard, it is going to slow down your report also, overall not worth it

[@monkeysnake77]

6:57 Just an FYI this works with Dynamic Measures as well :)

[@mattlakin5023]

Hi Patrick I am a new subscriber, great video. I just wondered if you (or anyone else) has tested this against using bookmarks and buttons (as shown in one of your other videos)? Is one or the other more performance efficient? I would have assumed that the dynamic table is quicker than having 2 charts, but then you are having to duplicate data... any thoughts?

[@rosswaterston]

Great video as always, almost spat my cornflakes when I saw the both directions, I've been telling my super user community to not use both direction, but to use crossfilter. I always quote your page though so at Tomorrow's session I'll be sure to tell the growing community to check out this video.

[@GuyInACube]

So you agree that CROSSFILTER is the way to go, which I said when constructing the DAX measure after discussing using BI-Directional filtering.

[@rosswaterston]

Yeah, using both direction filters can get you over a hump, but it has a nasty habit of coming back and biting you.

[@j-dude]

Mind sharing the reason it comes back to bite you?

[@rosswaterston]

Hello Jeff, setting the cross filter direction to both will work for you in the short term as Partick showed in his video, the issue comes in the future when you invariably are expanding your model, what happens in practice is that suddenly when you change options for a chart you get different answers, it leads to hours of fault finding if you don't know to start by looking for cross filter in both directions, but then changing it to single means something else will stop working. So you have to find what needs a crossfilter clause. Far better to recognise at the point when it is needed and implement and capture in your documentation (now available as field properties) that a crossfilter was used. I tried to find some better videos on it, I know Marco has a great one where he uses three tables to get three different answers using cross filter direction = both. Hope that kind of helps Jeff.

[@mcnater]

I think like most things it depends on the situation. Coming from the Qlik world, I'm much more used to and comfortable with the "Bi-Directional" Filtering as that is Qliks default if you will.

[@jenifferdemesa3962]

This is a great video. Thank you for sharing. Can you also do a video using calendar as attributes? I mean when I click monthly, daily etc?

[@GuyInACube]

We will take a look at it :)

[@sreeshanjeeva6665]

Hi, this video is very helpful Could you also suggest dynamic axis for scatter chart?

[@elmerparica5494]

Nice! How abouth date (Date and MOnth and Year) on the same visual?

[@dannyaguirre2309]

Thanks Patrick great video, I am actually working on a solution similar to this where I created new measures applying RLS, I have a business request to apply role level security to some values. However I ran into an issue, where the line chart visual, works and hides the values based on the security applied, however the label stays visible for the hidden value when I use blank(). Would this dynamic chart axis maybe help?

[@anthonylui90201]

Thanks a bunch for this @Guy in a Cube. Please is there a way this can be applied to scattered plot with both X and Y axis? Thanks in advance for your help. Cheers

[@alexkim7270]

Beautiful! Do we have lollipop chart in pbix?

[@lubeganasser694]

Hi.Thanks for the tutorial.Please give us a tutorial on substitutewithindex dax function

[@kseniiasmirnova5526]

Thanks! I have a more complicated problem based on that: what if I want to choose several columns to be displayed at once in a stacked column chart format? If I just choose several, I see the sum of their values, but I want them separately. How do I solve that?

[@fabiofantoni5247]

Great solution with moderate data volumes. Is there any chance to get dynamic axis without pivoting data and/ore relying on a unique identifier which is not that common for "digital" data such as web analytcs raw data?

[@vanipriya448]

that's awesome..thanks!

[@GuyInACube]

Most welcome! Thanks for watching 👊

[@badeyeq]

Hi Patrick, I found a solution to make a Y axis dynamic to the Max value when using a slicer. Kind of a very yukky solution but it works. I have contacted Microsoft piror to the solution and they had not found a way to do it so far. I like your videos a lot man, just thinking it might help someone else that had the same issue as me ;)

[@rightside1664]

You've just saved my life lol... thx, buddy

[@GuyInACube]

Love it! 👊

[@kartikeyatiwari6678]

Even mine ,drill through is far better then going to alter data model tables😁😁 🙏

[@stephenfryer5660]

Hi, My team and I are looking at switching reporting platforms and have been building concept pieces within Power BI, so far we have found everything and more, however, we are keen on making reporting not only dynamic from a user perspective but also from a production point of view. currently, we use SSRS and are able to have lookup tables with logic coded in and ready to be pulled through into multiple reports ensuring that where metrics are repeated any logic changes will be reflected across all reports at once. I've built a sample look-up table storing the DAX syntax needed for some of the measures used, then using the LOOKUP I'm looking to return the SUM()(example) calculation string from the lookup table as a formula, however, it's pulling through as a string value. Is there a way of having this type of setup to make reporting more flexible between our warehouse and Power BI? I hope this makes sense. Thanks

[@islamghaith9172]

excellent stuff, but my question will be, here you did duplicate the table to make the unpivot table but if you are linked with a live database that have a lot of changes happened, how we can make even that more dynamic? anyway that was a real good stuff.

[@vba1719]

Is there any way to do the same using attributes from different dimensions? I tried it by unpivoting a copy of the fact table, it partly works (I can change axis), but it breaks everything else - measures (I use Related Distinct Count pattern), cross-visual filtering etc.

[@BenOastler]

Great video Patrick! I've had a client want to add the rank to a x-axis of a graph. So basically, they want to add a measure to x-axis. I can't precalculate the values as they want to slice and dice. Any thoughts on this?

[@lenylenynka5795]

What if you want columns from more than one table, e.g. you want to filter by product or by teritory(which i guess you have in the Sales Territory table)? How can you do that, if the columns aren't in one table? But otherwise thank you, this tutorial helped really much!

[@priyeshjain100991]

Hi Patrick, I have followed this trick and it worked like wonder for import query mode. But somehow I have the same requirement to follow using direct query mode where I don't have unique row identifier

[@mercedehbc9416]

Thank you for your great Video. but the Question is, if we have drill trough feature, then we going to have problem for the second axis, it will work properly only for one (Product, Color) !

[@MrBobw53]

Any ideas how we could create a sliding filter for changing the y-axis? I already have a sliding filter for the dates of the x-axis of a line chart

[@martymcfly00333]

Very helpful video, but what if I want to change my axis on dates based on the number of days selected? IE: I have a date slider filter. If DateDiff

[@technicalcube6047]

Hi Patrick. I have a scenario. When I select colour or product, I want a slicer for them dynamically. I mean if I select colour from the unpivot slicer, then automatically a slicer for all the available colours of the model should show, and same goes for product. Unless we select any attribute from the unpivot slicer, any slicer won't come.

[@harofinassary1186]

Is there a way to split Product and Color into separate slicers?

[@kansalssagar]

Hi Patrick, How to make the decimal point appear in visual when values are lower than a particular (set) value?

[@hajnalsalanki1259]

How do you deal with having different units of measures in the values? Like %, and $ and unit in a single table. I struggled to display the unit of measures, so I ended up writing a Switch / Format formula to include these units of measures. The challenge however is that now the value become text (contains the UoM) and now I cannot use it in all visualizations :D Any advice on this?

[@pratikdoshi562]

Hey Patrick, The above videos is very helpful. I was trying to create a string parameter in Power BI, but when i do this, under modelling tab, new parameter, under data type it only says Whole number, decimal number or fixed decimal number. How can i create a string parameter? Can you help?

[@merctlou]

Very nice post brother..BUT how do you dynamically change the name if for instance you needed to display the actual name of the selected attribute on the axis.So not only the valuse to change, but the name of the column .

[@brentlightsey783]

Wonder about two versions of the visual. Then use bookmarks and buttons to toggle their visibility? Wouldn't scale to so many options as this pattern does, but has the benefit of not expanding the model size. Less complicated DAX and data model, but more complicated bookmarks.

[@wilver18]

You are great!

[@GuyInACube]

Thanks! 👊

[@flskimboarder592]

Seems like you did this with a pretty small data set, how do you recommend doing this with a larger data set? Having 2 data sets refreshing on server could be taxing just for the purpose of this.

[@tomashussey4314]

HI, An easy solution would be having both Product and Color as the X axis, develop 2 bokarmarks, one showing Product as the axis the other showing Color as the axis (changing it with next level in heriarchy) then palce 2 buttons associated to the corresponding bookmark.

[@GuyInACube]

Thanks for the suggestion Tomas! 👊

[@mohammedaminelachhabe2087]

I had the same idea , playing with the visibilité like in SSRS

[@francismationg943]

hi! tip is really helpful! thanks! One question, do i always need to re-create the Unpivot table if the dataset has new rows being added monthly? thanks!

[@AnshuSaket]

No, you don't, it will update automatically with dataset refresh

[@noviceuser9553]

Will this work for a trailing 12 month combo chart? One group would use a cancellation date and the other would use an order date.

[@RobertoStaltari]

Cool! Thanks

[@GuyInACube]

Most welcome. Thanks for watching! 👊

[@pantrycoupon7936]

Patrick, how can you l do this when l source from SQL SERVER OR SAP HANA does it has to be done from the backend. Thanks

[@anirbandatta3295]

Hi Patrick, I have a question on using filter (lets say a value range slider) on a Measure field in a Power BI Chart. I understand that Power BI does not support having a measure as a slicer. My scenario is like this. I have a stacked bar chart with couple of levels of drill down. The measure used in the chart is RequestCount. The chart and the drill-down shows request counts by operation names, status codes, % of success/failures etc. Please note that the report page also has other slicers which are applicable to rest of the charts in the page as well. Now, I would like to provide flexibility to the user to view this chart only for those operations whose request count for a selected duration is (lets say) more than 1000. Have you tried this type of filtering on a measure field before? I have built a work around using bookmarks by providing different pre-defined buckets of request volume (only one visible at a time, depending on a button selection). But the problem with this approach is whenever the user switches to a different view (bookmark) all the slicers previously selected by the user get reset. Would highly appreciate if you can share any thoughts around a more genuine and reliable solution.

[@Gnomfresser1]

Hey Pat, great job! I like the way you present, taking the viewer by the hand and guide them through every single step . However, I think you beat about the bush a bit too much sometimes. It would have been possible to explain all this in a 5 min video. I might be a bit too harsh, maybe just review it yourself and think about it. Love your content! Thanks :-)

[@likhithareddy2667]

How to approach if the columns are of different data types??

[@hi_vishy]

Is it possible to dynamically change chart axis format, say from absolute to percentage on change of filter or selection. Want to show sales or sales growth in trend chart for multiple products.

[@pavlecoric8058]

Hi Patrick, is there any way to implement this using a calculated column?

[@tejeshgour1919]

This option will not be available when we are connecting with AZURE SQL server(tabular model)

[@gabaynoam]

Hay Patrick! Great video... Seems like you aren't lazy just very efficient. I used this method and it's great... Now let me challenge you a little bit. What if I want the unpivot slicer to slice 2 visuals, one with the selected value and one with the unselected value. So as your data example you'll have table with color(in case color was selected) and a bar chart with CountOfProducts (products on x axis is the unselected)

[@nehashahpatel1741]

Millions thanks

[@shrutiupari]

Hi Patrick, I like the way you make the videos which helps me understand the concepts easily. So I have some query for which if you could reply me, I would be really thankful

[@DJSTRYD55]

I've tried to apply this workflow to a scatter chart but to no avail. Would this, or another workflow, be applicable to the Y axis of a scatter chart?

[@reynardresuello7905]

Hi, Is there a way, to make a charts position variable? so it can move across the canvas depending on its values.

[@tapsisharma2800]

Can this be used in case we need to change the line and clustered column chart dynamically. I know it does work in bar chart but how will this work in line and clustered column chart?

[@davidcornetet2316]

I'm using a stacked bar and line chart with primary and secondary Y-axis. Power BI automatically assigned the columns to the primary and the lines to the secondary. Do you know if there is any way to change one of my line graphs from the secondary to the primary axis?

[@SmackMattAllonDaFl00r]

Hey if anyone isn’t able to use this trick because it makes your data set too larger, I found that you can create an example table for the data to pivot on. Make sure the example table has columns = value, attribute, & each attribute as its own column. Then All you need to do is switches the active relationship based on the selected slicer value (X axis value) using the USERELATIONSHIP function 👏.

[@AW-pp4jg]

Hey Matthew, can you share more insight on this approach you are talking about. I have large data set, and this approach is good, but it blows my data way up. Thank you

[@gobhardwaj]

Can we do this with a live connection? When we have a huge dataset?

[@jyotidatta2126]

I have to slice as per Country Product and Manufacturer and they all are in 3 different dimension table. Please suggest how should I go about it?

[@GreatIndia1729]

Very Good,

[@jerrynthomas147]

Really cool and easy to understand! :D And to add to it, he is funny as well! :D Think you can do a dynamic hierarchy tut the same way?

[@GuyInACube]

Thanks Jerryn! We will look at doing something like that. We want to do more tutorials like this one.

[@Tomoyochan10]

Guy in a Cube Hey, any updates on this? :) Would love to be able to use a slicer to dynamically change the x axis to the next level down of a hierarchy.