Filter top 3 products with a row for others using DAX in Power BI
Vložit
- čas přidán 10. 07. 2024
- This video shows an optimized DAX technique to display the first N products for each category in Power BI, adding a row that aggregates the value for all the other products. Article and download: sql.bi/695263/?aff=yt
The article includes further insights and optimization solutions, whereas the video introduces the scenario and explain the simpler formulas.
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... - Věda a technologie
I loved seeing how even Alberto spends time looking for a missing parentheses and forgets the return part. I somehow feel less of an idiot now.
Agree. That's adorable:)
You have just helped me solve a clients requirement I have been trying to fix for over 5 months! WOW thank you!
Excellent! Easy to follow, easy understand, and relatable. I'm amazed at how fast he figured out the Rank+1 miscalculation at 34:00. Great tutorial.
The value of this video is priceless! Un caloroso GRAZIE dalla Sicilia, Alberto!
So simple, yet so complex. Excellent Alberto!
I really like your way of presenting, slowly but without any gaps in knowledge for the audience.
Everything is clear little by little, thank you very much for the useful information
Truly masterful. I love peeking into Alberto's brain and see how all the pieces come together in there. A long video, but worth it! Thank you, Alberto!
That's truly a masterpiece. It's so complicated, yet you make it so understandable! Thanks very much for the video, sir!
Thank you so much Alberto, I love how passionate you are about getting to perfection with DAX. Your love for your work is truly inspiring for your students.
Dear SQLBI team, thank you for your work. Your presentations, like this, are helping me at work.
Bravo! This is pure gold.
Thank you for sharing, Alberto
Fantastic Alberto!
Thanks a lot for this so useful tutorial. Not only to learn more about DAX, but also for its application to the customers requirements.
Fantastic Video. So helpful. I watched once, then managed to put together what I needed (Pie Chart rather than matrix) using your technique and it worked perfectly. You explained each step so well.
Hi Alberto, this was actually very easy to follow up and the fact that you did it in PBI desktop helped a lot. I followed your instructions and got the results I wanted right away. You are so awesome! Thank you so much!
Thanks Maestro Alberto for the video. This demo shows how important logic and creativity are to good coding.
Knowing DAX combined to logic and creativity as you said can solve any scenario you encounter!
As always great stuff,
I like the way of providing outlines in the beginning of the video and showing organized breakdown of how you are going to solve the problem.
As always great lesson from great teacher.
Thank you Alberto
Thank you SQLBI team.
Alberto once again knocks it out of the park. I was asked by my Exec to do this very thing in December 2020, I wasn't finding very helpful instruction elsewhere, Alberto's step by step instruction were great.
Greeting from Central America, I'm learning a lot with each video. Thank you
Congrats Alberto and team, It is noticeable the production quality.
Your videos are the best videos I've ever seen.
Many many thanks for all your work.
First it seems like "Mission Impossible",but Alberto cracked it. Excellent.
Tension mounts up in a live workshop after a satisfactory search of a good 30 secs for the missing part, experienced the same bermuda spot many times as a trainer. No matter how great you are, it happens for everyone..
I have expected a sophisticated formula for this calculation (coz many new functions were born after 2018 in PBI), but still so many nuts and bolts needed for this Top N products after so many years of product lifespan, I am talking abt Top N.
But some DAX patterns were changed i.e budgeting and Basket analysis after the advent of new functions..coz I still remember watching the good old days of Budgeting in Power Pivot and Power BI in the 2015/16, tens of times for good understanding.. Thanks Alberto..meet you one day in India..
This is exactly what I did in my previous project, the small change was, instead of making placeholder for others row in the middle, we placed at the end by counting distinct products +1 , then row visible filter logic was to show all products rank
I been spending hours to try to solve this problem! Thanks for this video!
Brill! Thanks for giving that fantastic solution.
Not that easy, but I was able to recreate everything and I will present it to my fellow colleagues soon.
It is always a pleasure watching your informative and useful vids.
Cheers, Vin aka Pia
This video is so useful. Thank you so much for this.
Special Thumbs Up for the way you explained the whole Technique.
Alberto you are a master!!! great video, I spent a lot of time thinking about the logic used to solved the problem and this is just excellent, easy to understand once you follow your recommendations. Thank a lot please to continue creating sensational videos as this one
Wow.... excellent ! ... solved what i want to do in my management report , step by step on how to do it ... Perfect !
Excellent handwriting, great solution ( thank you for the video ! )
Another great video from Alberto. Thanks!!! it's pretty awful the lengths we need to go to to work around Power BI's shortcomings. I'm having to use Power BI now because my company has moved away from QlikView. In a QlikView dashboard you can select a Top N as a static or user variable qty and the visuals' properties have a lovely checkbox to "include others" and QlikView just does it - in a split second, no further coding, lots of time saved to do something productive instead.
Breaking complex requirements into smaller pieces makes it easy to understand. Looking for more videos ..
A very useful and understandable topic! Thank you! Time to start the book!! :)
that was amazing and inspiring! I'll be watching again and practicing
OOOhhh ... a heavy dose of DAX!
Thank you Alberto, cool report.
Thank you for the detailed explanation. This helped a lot!
Awesome video Alberto! You've done my life much easier. Cheers!
This is better then a Ferrari, it is Sleek and awesome like a Ferrari and yet it is available on you tube for free.
Alberto you are GOOOOOOOD!
A very very good video, thanks for sharing your insights and methods on making this customization.
Just practiced it with my dataset. it works like a charm. love the contents and i am reading thru your book as well. thanks.
Good to know it works for real! :)
Thanks for sharing it.
Amazing video, thanks a lot. I am learning so much in your channel.
Thank you very much for this great tutorial!
Thank you for this video. It has been very helpful!
Thank you so much... you are a truly teacher
WoW......I have seen that video thrice to understand the concept.....Thank you so much for sharing your knowledge👍👍👍....
Thank you very much Alberto. Very good as always
Simply Love it sir. Excellent job.
Thank you very much, good, good, ideas and resolution is perfect.
Thank you, it works perfectly! I had to twek the formulas a little because my project works with YTD :)
as always, I have to watch 2 times. Love u, Alberto :)
Excellent tutorial. Really helped me a lot
Thank you so much Alberto. be blessed
Excelente video, genial la utilización de los parámetros para volver el TOP dinámico y genial la manera como se resuelve por medio del condicional del rankin la fila "otros" para las visuales.
Muchas gracias.
Cool. I've done my project by your video :D Thanks a lot
Thank you Master, very didactic class!!
VERY USEFUL!!! Thanks Alberto!!
Awesome, it definite takes reports to the next level. It´s difficult but it worths.
Amazing, Alberto. Thanks for sharing your knowledge with us 💕💕🥰🥰. Come to Brazil and bring Marco too. 😁😁.
Great and very clear ! Thank you.
Great thank you for all videos
Thanks a lot Sir for the detailed explanation
Awesome video! You're amazing!
Truly Genius, Alberto! Thank you very much and Marco for all your work. I started studying DAX two years ago with SQLBI and your patterns. I wanted to ask if it is possible to use MDX inside PowerBI, I sometimes use it creating named sets in Excel and was wondering if you know any features or secrets about it. 🙂
Thanks Alberto. I know what my team is going to work on today :-)
Great video thanks!
Thank you for another excellent and educational video! These are really appreciated!
After watching the first video on the TopN function in DAX Studio, I had the same thought as the other viewers like you mention in the start of the video 0:49..
Though this video was excellent, what I am "missing" is link between the complex query in DAX Studio and Power BI. How do I, step by step, code piece by code piece, move my query from DAX Studio to a useful measure in Power BI?
If you could make a video on this it would take the understanding of DAX for a lot of us DAX developers to a whole new level.
Your videos are top notch! Its not just the knowledge but your presentation of it is essentially flawless. Great content, great audio, great video (using the stream deck :thumbsup:) and doing the on-screen inking is the cherry on top!
I have to ask, since I'm currently in the market, what's the tablet you are using for that (and app if you needed one)?
Thanks. I am using a Samsung Galaxy Tab S7+ with Noteshelf as the software (but the sw to draw depends on the video I record). It is then connected through USB and mirrored using scrcpy. The main drawback is that you need a picture to draw on, therefore you need to capture it in advance.
Muy inteligente profe. Gracias!
this is amazing. I thought you were writing it on the spot. regardless, genius
Eu sempre fico maravilhado com podemos voar apenas com alguns minutos de conhecimento de qualidade!
Como sempre digo: "Aprenda algo novo todo dia". Esse foi meu novo de hoje.
VAleu!!!! #powerbinareal
Great, Alberto! I would add a little change: 1. If there are less products that TopN, then no need for Other, so it should return BLANK() and not zero (at least in my scenario).
Thanks!
Great video!
Great tutorial
It's wonderful for me👍 thanks
Numero uno, grazie mile
Great video thanks
Sempre TOP!
Brilliant!!
tnx very much! awesome!
Brilliant!
thank you very much
Amazing!
a masterpiece!
You are amazing 🎉
Thanks Alberto, as always very well explained
Ideally would have the year as a column in the matrix, so one could compare the rankings over the years 🤔🤔
Thank you
Thank you so much for the excellent video, very well organized and presented. One suggest, if I may make, please please make the size of your curser larger or make it transparent, I had difficulty following where you were clicking.
You are awesome.
You are amazing 🤩
Super , very useful
Fantastic..
very useful vid! I would like to group up the blank values with the 'Others'. How would I achieve this?
awesome 👏👏👏👍👍👍👍
He is a master, but this difficulty level for creating just Others shows how MS Power BI has a profound problem. Tableau solves this dynamic issue with a click!
Yes, this is a workaround until Power BI implements a specific feature in the UI!
Top notch video! Thanks a lot for this. I have a question tho. Instead of a Whatif table to generate N values, I limited the slicer to show top 5,10,20 and 30, and all worked well. However, I also want to add an “All” option to the slicer, when I choose which all the data will be displayed in the matrix, sorted descendingly. I’ve been trying to solve this for a while but couldn’t. Do you have any suggestion? I’m very grateful for any help.
Alberto, instead of assigning a dynamic rank to the "Others" item, you could take the absolute max from the TOPN table, add 1 to it, and assign this to the "Others" item as the rank. This way you don't have to think too much about the arithmetic and it'll work just as fine. Of course, in the measure that governs the display of the rows you'd need to just add an OR condition to account for the "Others" row. And the ranking could still be used for sorting. The basic idea here is to assign the (max of the TOPN table + 1) to the "Others" item. If you still don't want to think too much, just assign plain 10000 to "Others" as its rank and account for this in the row-filtering measure as before using OR. Sorting will work fine again.
Did you measure its performance? We don't think it would change the performance much, and the code would be very similar - but we might be wrong!
Great video. Very educational. I think the Top3Prods at 27:00 is unnecessary, it is already down to these top product (and others but the value is zero in this stage).
Great Video Alberto.
I have a question. I add the customer name to the rows. I can't understand why i am getting rows with no sales. What should be changed to not show those rows?
Thanks Alberto for very helpful tips. One question about Ranking measure - why you didn't use [TopN Value]+1 instead of 4 in IF( IsOtherSelected, [TopN Value]+1... This way you can filter not only TOP 3 but TOP N products.
Hello Alberto, this was awesome and thanks, I have one last question, If I want to display the information in a chart instead of a table, how do I sort the data in a graph similar to the table view. ? please do help me with an answer.
Hello! I have tested this and it’s really great. However I have tried to use another type of visual: bar chart/column chart and it seems that it doesn’t work as expected. Is this approach working only for matrix/table or am I missing something? Thank you!😊