Power BI - Dynamic Axis via Slicer (No DAX)
Вставка
- Опубліковано 30 лип 2018
- In this video, I show you how to dynamically switch your X-Axis via a slicer selection!
Enroll in my introductory or advanced Power BI courses:
training.bielite.com/
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
TinyLizard Blog Post:
tinylizard.com/dynamically-cha... - Наука та технологія
Really, really helpful video. Dynamic legend/axis selection is an important function and the method for achieving this in Power BI was so wonderfully explained in this video. This is genuinely one of the best Power BI videos on UA-cam.
100% agree with Amr Ibrahim - I was almost on the verge of giving up achieving this and then I stumbled upon this video. Many thanks to you!
I am coming here after two years , Thanks a lot Parker for this without writing DAX You just saved my day as I was trying to do with Dynamic M query and it was not possible when I got to know that we cannot work with the Dynamic M query if the data set is connected with Excel based or Access based or may be sharepoint connection , This is really helpful! Thanks a lot :)
This video alone is probably going to get me a raise when I make a report for my work. Thank you sooooo much this was fantastic. I've watched a few videos and I love the way you explain things. I will be subscribing and look forward to learning more. Thank you for taking the time to make these.
Hahaha that's great to hear! Thanks for the sub :)
I am so thankful to you for making this powerful feature so easy and without any DAX. Thanks you so much Parker.
This worked like a charm. Thanks for showing this so succinctly!
Simplicity is the mother of beauty 😍
I like how simple this was.
Simple and powerful.
Thank you for sharing this.
+Amr Ibrahim haha you're very right! Thanks for watching
This is totally awesome but my Query is really big (2mm rows) and I need a lot of dimensions to filter (15). Is there a way to do it with a Table, without having to duplicate the Query?
Thanks!
thank you for this tip. I use that extensively, especially with matrix visuals to add and change columns dynamically. You can create another (second) data unpivot table so that you could have 2 slicers able to dynamically add two columns in the matrix. I am looking for the way to have only one slicer with all my dimensions in it (all collumn names) and multi-select those, getting as many columns in my matrix visual, as the items selected in the slicer. would you see any way to get such functionality in PBI?
Thanks for the tutorial! Is there a way for the axis title to dynamically change as well, rather than be set on "Value"?
Great tip by BI Elite. Is there a possibility to use the same trick with multiple columns from multiple tables. How can we apply the same trick for the above requirement.
Amazing. Thank you. Great tutorials. Very useful.
You save my day. Thank's for this. Keep going man!
Haha good to hear, Esteban!
What if the columns you want to switch between belong to different tables? In that case, is there a way to implement this solution?
Well explained, I used the trick but without removing the value column, which makes the model lighter. Thank you:)
No problem Vitali! Thanks for watching
Very simple and good explanation brother..
Is there any way to do the grouping and filtering the same time with this trick..? For example the above we have Name and Product groupings. As well as under Name group we have 3 names. By doing the grouping with Name, can we filter the Parker...?
Thank you. Watched the video many time. However not able to bring the data at my end. I am pulling up columns from multiple tables to make a DataUnpivot table and adding index on all the Dim and Fact tables so as to associate with each other. Then relating the tables with Index columns, but the result is not getting achieved. Will you please advise in this regard?
Works perfectly. Thank you for the clear instructions.
Thanks for watching, Dhruv!
Can it be applied for a measure instead of dimension (for y-axis in case of line chart)? i.e. x-axis constant dimension of month-year and y-axis (dynamic measure, say sales or profit or sales_lag) to be dynamically chosen through slicer.
When I use this method charts take forever to display. My data table is 500k and I have 7 attributes which bridge table becomes 3.5 m. How do I get around this?
Hey, thank you, great Solution. I still have problem tough. Because I want to slices may bar chart between Date, Week of the year and Month. If I use the value column as X axis, I cannot arrange them in the correct chronological order. Can someone help me?
This works but it seems to corrupt if there is a database refresh. Do you have to recreate these steps everytime you refresh the source data?
I am trying this and it works, but the index columns are changing when I refresh the data, so I get different values when I filter the same index of pivoted and unpivoted table, any idea?
Is there a way in which we can use two dimensions in X-axis instead of just 1
I'm trying to set up my time x-axis for my assignment. Hopefully you can help @BI Elite :D
Very clear presentation.
Very useful video! Thank you very much!
Is there a way to do this with dates? The slicer being a date range and the values are numeric
Hi,
I have multiple dimensions type columns in multiple tables, I want create table with desired columns Headers and put slicer in report and when we select any dimension report dimension has to change as per slicer selection, Please help.
Thanks in advance.
Suresh
Can I do this for legend too instead of axis?
Good work....
But I am strucked at the point where I have 32 charts so as per your explanation I need to make 32 measures , can't make it simple by making 1 or 2 measures and use it in every chart
Thank you for the very clear presentation.
A question re the Power Query Changes!
Would the solution still work if you did a "Reference" rather than a "Duplicate" in Power Query?
Ted
Great question. Yep it would work the same. You would still be loading in two separate tables at that point, but your suggestion would make it a little cleaner
I liked the trick, but I have another one harder step that I believe would be equally challenging (Idk if you already have a video on that, if yes, let me know: what if I want to have a bar chart with 2 cities and I want to know their population from a database of cities and how much population each city has. However, I want to individually select each city so I can compare each cities' population individually. How would I do that?
I must be doing something wrong somewhere, but going through the video several times, not sure what. My visual is a stacked bar-chart. I was able to create the attribut slicer, but when I select the attribut, it displays the attribut and the total. It doesn't display the different attriibuts. Basically, I get one bar rather than multiple bars...Is this because I am trying to apply attributs to a y-axis? In which case, is there a better / different approach for Dynamic Y-AXIS?
Brilliant, thank you
Hi, very nice can we do the same using dax !?
Its amazing!
Thank you so much!
what is name Parker item Bike, had 2 entries in your data table, would it have an issue, do you need to remove duplicates in your unpivot?
Great Video
Thanks!
Thanks a bunch for this @BI Elite. 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
This same method works with scatter plots.
I can't thank you enough 😍
Furthermore on creating the DataUnpivot and applying the changes, the DataUnpivot takes forever to load :(
Hi! if data from the main table changes? should i re create the unpivot table?
Hi Jayson, if you use the "unpivot other columns" technique, I don't think you will need to!
Awesome Tip!
Thanks for watching Claudia!
you are awesome bro....very smart trick... it helped me in my project.
just putting forward a point. you have created a duplicate. it would have saved some space if it was a reference .
what is your opinion on that
Thanks and Regards
Kishore
You're very right! Thanks for the tip
This trick has worked really well with my smaller datasets with less complexity. I tried this out on a pretty large dataset, and am able to update the group by correctly, however, the individual row values are not the same, only off slightly. Its odd though, because the totals of the new table, and my old one are the same. Any tips?
Hey Ryland, sorry I don't have any tips for that. I haven't experienced what you're talking about but thanks for bringing it up. If I ever run across this, I'll remember your comment and come back here if I find anything out.
I am having the same problem!
Great tip
Thanks Hermes!
How would you solve this if you wanted to change the Y axis instead of the X axis? i.e. if you wanted to change the values being measured?
For example on the X axis you could have the names of products you are selling. On the Y axis, I would ideally like a slicer which can switch between the total value of sales and the total number of units sold.
+Philip Butler I made a video on exactly this! Check it out here: ua-cam.com/video/OIGGrrnMIgk/v-deo.html
I am stuck at making an index, which i want to do as a calculated column in existing table. I tried RANKX but it gives the same number for the same date
ended up just doing it in SQL so it just loads with the table. way overly complex to do a rank on 2 columns in dax
Great!!!!!, many thanks.
does it still works if the table is updated?
I am trying to keep the behaviuor after table updating as well. I need to keep it working after table update, or otherwise automate the unpivot table creation. Can anyone help me?
@@igizan Whatever changes you make in the base data will be reflected in both tables automatically
@@igizan I'm having the same issue. When data is updated in the primary table the unpivoted table is not getting the latest values. Have you found a solution for this? I'm wondering if the suggestion from @kealkil4 to use a "Reference" rather than a "Duplicate" in Power Query would work?
@@joecronk6158 I'm sorry Joe, but I don't remember how I solved the issue. In the meanwhile we (at my job) opted for another data viz tool
caw we use dates on x axis ? Slicer will select year, quarter, month, week, daily scales.
I also have the same issue. My calendar table is created in PowerBI using DAX. Appreciate if you provide an option. Thanks
This is great! I was trying something with TREATAS and CROSSJOIN. Can we have something for a dynamic Y AXIS selection along with a X AXIS selection?
Nice! Check out my other video for dynamic Y-Axis: ua-cam.com/video/OIGGrrnMIgk/v-deo.html. It's even easier to set up than a dynamic X-Axis
@@BIElite - thank you for the quick reply! Just to confirm this can be used in conjunction with having a dynamic x axis + a dynamic y axis for the same visual?
@@nirvana_moksh I've never tried but I think it would work! Give it a try and let me know if you have success!
I just used the same X axis format for Y, by creating a duplicate table and keeping all fact related columns and it works! I think the only drawback I see is the table is being loaded three times I guess. Otherwise I think this is more maintainable compared to having a DAX doing crossjoins for all the measure slicers first and then using TREATAS. Thanks again!
+Nirvana Moksh nice! Glad to hear you got it working! I will have to try it out myself
Brilliant!
Thanks Ryan!
How date? (Switch to date and month on the X-Axis?)
I have the same question/issue.. being able to display Date Range (e.g. MTD, QTD, YTD) in x-axis chart as attribute..
Raptor Quality Approved
Cool one
¡Excelente!
Thanks José!
Like a charm!
Glad to hear! This is a cool trick, indeed.
many thanks.
Thanks for watching!
Thank you
+sadyaz64 and thank you as well 😊
neat..very neat!
Agreed! This is a lot of fun.
Good trick
I appreciate the feedback Suresh!
BI Elite for any queries how do i contact you?
+Suresh Vagale feel free to reach out via email at powerbielite@gmail.com
Omg...genius
Yeah I love this trick! I use it all the time
@@BIElite one query in chart. Need column and line series on primary axis and another line series on secondary. This is possible in Excel. Is there any custom visual with this functionality?
+Vishesh Saxena I'm not sure about excel, I only know that this is not supported in Power bi. If you are trying to do this in Power bi, it might be possible with the use of an R or Python visual. I have a video on Dual-axis line charts in R that might be able to get you in the right direction
Wow...!!
+Singireddy srinivas reddy Thanks! Hope you find it useful
Ofcourse...!! As always very useful tips and easy way to understand your tips..!!
Fantastic work! I've been trying every posting (including @GuyInACube) to no avail, until I found your tips. One question though...How do I NOT have an Attribute Slicer and set the Attribute value from a Measure? This would help ALOT!