This is extremely helpful not just because "RANKX" but even more to see the step-by-step iterative approach you use in developing a complex DAX expression. I easily get intimidated by seeing the final DAX expression and then enjoy some time to reverse-engineer. But here, the simple-to-complex evolution of the expression is revealed and a great help. Thanks for providing this -- your books and videos have changed my professional life a huge amount and I always enjoy everything you and Marco put out for us!
Truly masterful. I learned not only specific DAX techniques but also (and more importantly) how to generally approach more complex problems methodically. Thank you, Alberto!
Wow! That's 9 DAX functions explained in one solution. Thank you for sharing this brilliant approach to writing a complex solution piece by piece in Dax Studio. That incremental approach to solving the problem was most educational and I look forward to many more fantastic videos from yourself and Marco.
Amazing video, in just 15 minutes I learned a lot. Until now I have used GENERATE just for make some joins, I was not aware that the second table is evaluated in the context of the row of the first table.
So totally agree with the 'break it down step by step' approach. Honestly, it's the only way I can even hope to tackle a complex DAX expression. (And believe me, my use of the word "complex" here is relative!)
I love it! Can’t wait to try it! Thank you thank you thank you. I’m coming from the Oracle SQL world so all of this is very familiar to me and easy to implement in sql, but I had no clue of to write it in dax! You made it easy. ❤️
Loved watching the way you solved the problem step by step and kept in the clip the way you fixed the errors because it showed the problem solving approach. Learnt a lot and was able to use in my own model in Power BI, how do you keep the order by step though?
That's awesome. I had such scenario in my previous project and have done exactly same but in ssas tabular. However, didn't had to see within each year (or any other column). It was simple to only calculate top 10 countries with others row. But was quite challenging to incorporate the external filters. I hadn't used generate function. Good to know and appreciate some such scenarios. Request for a video on handling different granularity and working with multiple currencies. Thank you very much. Enjoying DAX 👍
Some time ago I was building similar thing. I did it, but not in such elegant way like yours. It has a great use in Pareto chart, to group the tail nicely. The only problem I have with this is that this is still the table, which is refreshed with dataset. It doesn't react on filtering to be calculated "on the fly". I used a report built with Qlik, it seems that they have a solution to this problem. If, by any chance, it exists also in Power BI, I'd love to see it. Thank you for very good video!
Power BI does not have a specific feature for this scenario, we provided a workaround in this article: www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/
Fantastic!!! It is building from DAX 101 to DAX 401. Gradual build to epic. I am finding it difficult replicating it in Power BI. i would appreciate any assistance with work around in Power BI
Excellent video to create top products along with other other product information in the other row. Amazing😍😍.But how can we create same expression in measure. Is there a way to create a measure?
Could you demonstrate how this example would be transferred to Power BI? I'm not familiar with DaxStudio and am not quite sure how you'd build this with measures, etc. in Power BI.
This is brilliant content. As others have said aside from the useful business problem you have solved, watching you go through the process is invaluable. Is it correct to say the only way this result set can be displayed is via a calculated table? Would this be the main use case of a dax calculation like this?
Yes, for a calculated table or for a paginated report. However, if you are interested there is an example based on measures producing a report in Power BI in this article: www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/
Real DAX masters. Thanks for the efforts. One question is, can't we able to write it without Dax studio. If want to write the same thing in measure that also works fine correct. Is my understanding is correct
This might sound like a very stupid question, but where do we use such a query if a measure can't return a table ? The only use I can see for this is to create a table and that wouldn't really make it interactive to other filters. I'd appreciate if someone explains use-cases of such a query or how we can insert it into a table visualization.
We have an article describing that use case: www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/ We will also publish a newer article and video later with a better technique (not ready yet). The technique shown in this video serves two purposes: 1) Create a report in other environments, such as Paginated Reports and Excel (you can dump the result of a DAX query in an Excel table). 2) Provide a longer example about how to write DAX code, dividing a problem in smaller simpler steps that are easier to write and test.
Hi I have a query for you I have five tables in my data model if I change the existing relation using userelationship function on one connection, How can I get the child data from the other tables using the new connection? It is an interview question
Hi Alberto! Can you point us to a web page or video that explains how SUMMARIZE works? All of the ones I find just show the depricated version that addes named expressions. I can't find anythat explains what is returned using just TABLE and GROUPBY. Does it just sum up the other columns? Concatenate strings? What about dates? Thank you!
Read these articles (just use the first part of SUMMARIZE without the aggregated columns): www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/?aff=yt www.sqlbi.com/articles/all-the-secrets-of-summarize/?aff=yt
if you have only 2 products in 2009 the rank will show 1,2,4 (for others) wich would look weird :P (i know it's a Big what if only for educational purpose ... :) :D )
Hi Alberto. Thanks for this great material. One question I'd have... If I want to return the result but without the auxiliary column that enables me to obtain the correct sorting order, would I have to wrap this result in SELECTCOLUMNS? Would that be enough to keep the sorting order? Thanks.
The column used for the sort order must be part of the query (SQL is different on this regard). A workaround for a limited number of rows is using hidden characters like in this article: www.sqlbi.com/articles/handling-customers-with-the-same-name-in-power-bi/ But you can hardly get this working with large datasets.
@@SQLBI Thanks for the quick reply. Do you think it would be hard for M$ to implement the possibility of returning a sorted set without the sorting column?
This was great article. I was wondering why you used the @ColumnName for the column name since it also could used as a parameter. Are there any newer articles that demonstrates how to use it as a parameter that parallels the use of parameters in SQL Server. (www.sqlbi.com/blog/marco/2012/01/05/use-parameters-in-your-dax-queries/) is the latest article that I could find.
The @parameter syntax is not a parameter if used as column name, such as [@ColumnName]. For example, in this code only @Limit and @Name are parameters: EVALUATE FILTER ( CALCULATETABLE ( SELECTCOLUMNS ( 'Product', "@Name", 'Product'[Product Name] ), 'Product'[Color] = @ColorName ), [@Name] > @Limit ) However, it is clear that in a query where you have both parameters and temporary columns, you should evaluate a different naming convention for the column names. The reasons why [@ColumnName] could be a good naming convention (when you don't have parameters) is described here: www.sqlbi.com/articles/naming-temporary-columns-in-dax/
You must add some other element in the sort order - unfortunately TOPN only gets one measure, so you have to figure out an algorithm that uses a second column to make a final sort order (which could be hard, we know).
I haven't managed a single example from your tutorials so far, when I aggregate according to my turnover I get an error message that (that there is no match) with the other example at Summarize I couldn't even type it out 1:1. Your examples are too worthless or not complex enough. Maybe you could explain what kind of data you are using and what kind of error handling would be possible.
can somebody please explain me how to sort by multiple columns in PBI Desktop as made by the last code line? - If we are in the situation where we have the same product for different @POS values, this can't be done just by using "sort by column" command. - this only if we don't wanna use @POS into our matrix/table, and if we can't use the index column of power query. Thanks
see these: www.sqlbi.com/articles/handling-customers-with-the-same-name-in-power-bi/ www.sqlbi.com/articles/sorting-duplicated-names-in-a-level-of-a-hierarchy-with-dax/
One of the best videos he has ever made, coming from a DAX Grand Master. A true pleasure to watch, I could watch these all day.
This video is the answer of thousands of questions on the official Power BI Community. Thanks a lot for sharing!
This is extremely helpful not just because "RANKX" but even more to see the step-by-step iterative approach you use in developing a complex DAX expression. I easily get intimidated by seeing the final DAX expression and then enjoy some time to reverse-engineer. But here, the simple-to-complex evolution of the expression is revealed and a great help. Thanks for providing this -- your books and videos have changed my professional life a huge amount and I always enjoy everything you and Marco put out for us!
Thanks a lot for continuously sharing your immense knowledge with the BI community
Truly masterful. I learned not only specific DAX techniques but also (and more importantly) how to generally approach more complex problems methodically. Thank you, Alberto!
Wow! That's 9 DAX functions explained in one solution. Thank you for sharing this brilliant approach to writing a complex solution piece by piece in Dax Studio. That incremental approach to solving the problem was most educational and I look forward to many more fantastic videos from yourself and Marco.
Amazing video, in just 15 minutes I learned a lot. Until now I have used GENERATE just for make some joins, I was not aware that the second table is evaluated in the context of the row of the first table.
OMG Alberto you are truly a DAX Ferrari:) fantastic!
So totally agree with the 'break it down step by step' approach. Honestly, it's the only way I can even hope to tackle a complex DAX expression. (And believe me, my use of the word "complex" here is relative!)
Thank u so much. BI community is indebted to you
Oh, my god, I just realized who you are! So glad to watch these excellent videos. Thanks.
Amazing video, i am happy that youtube recommended this to me.
Third time I watch this video and finally got it !!!! thanks so so much for your dedication Alberto!!!
I may take my entire life to write dis kind of codes 😅😃😃
I love the way you explain the topic!!!
Greetings from Poland. :)
Wonderful. Your explanations are so clear and it seems so simple when YOU do it! Thanks so much.
Clear step by step approach explained in a simple way.....👍👍👍
I love it! Can’t wait to try it! Thank you thank you thank you.
I’m coming from the Oracle SQL world so all of this is very familiar to me and easy to implement in sql, but I had no clue of to write it in dax! You made it easy. ❤️
Just WOW...you are outstanding Sir. Thank you so much for sharing.
A masterful demonstration.
Stellar stuff Alberto!
Thank you for sharing your knowledge!
Perfect example.
Loved watching the way you solved the problem step by step and kept in the clip the way you fixed the errors because it showed the problem solving approach. Learnt a lot and was able to use in my own model in Power BI, how do you keep the order by step though?
Great DAX Video i have ever watched.
Simply superb, well done Alberto.
Wow! Next level DAX.
Amazing! Really really useful! Thanks for sharing this content.
Amazing video to watch, thank you!
Code clean as a whistle .
Great video, very clearly explained.
Excellent presentation. Thank you
That's awesome. I had such scenario in my previous project and have done exactly same but in ssas tabular. However, didn't had to see within each year (or any other column). It was simple to only calculate top 10 countries with others row. But was quite challenging to incorporate the external filters. I hadn't used generate function. Good to know and appreciate some such scenarios. Request for a video on handling different granularity and working with multiple currencies. Thank you very much. Enjoying DAX 👍
Great example
Some time ago I was building similar thing. I did it, but not in such elegant way like yours. It has a great use in Pareto chart, to group the tail nicely. The only problem I have with this is that this is still the table, which is refreshed with dataset. It doesn't react on filtering to be calculated "on the fly". I used a report built with Qlik, it seems that they have a solution to this problem.
If, by any chance, it exists also in Power BI, I'd love to see it.
Thank you for very good video!
Power BI does not have a specific feature for this scenario, we provided a workaround in this article:
www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/
Fantastic!!!
It is building from DAX 101 to DAX 401. Gradual build to epic.
I am finding it difficult replicating it in Power BI. i would appreciate any assistance with work around in Power BI
How many years I will need to become an expert like Alberto?
Good video, I would like to figure that with Spark SQL's Window function, we can get things done as well.
Excellent!
Very helpful. Thank you!
Great video, thanks
Excellent video to create top products along with other other product information in the other row. Amazing😍😍.But how can we create same expression in measure. Is there a way to create a measure?
Use this: www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/
Thanks, very good...
How do I become good at Dax? This is so inspiring...
Read Alberto and Marco's books, watch videos and takes their courses. Best investment you can make
extraordinary!
Could you demonstrate how this example would be transferred to Power BI? I'm not familiar with DaxStudio and am not quite sure how you'd build this with measures, etc. in Power BI.
See this article+video: www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/
This is brilliant content. As others have said aside from the useful business problem you have solved, watching you go through the process is invaluable.
Is it correct to say the only way this result set can be displayed is via a calculated table? Would this be the main use case of a dax calculation like this?
Yes, for a calculated table or for a paginated report.
However, if you are interested there is an example based on measures producing a report in Power BI in this article: www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/
Execelente explicación
Real DAX masters. Thanks for the efforts. One question is, can't we able to write it without Dax studio. If want to write the same thing in measure that also works fine correct. Is my understanding is correct
The video shows how to create a table expression, you can use the same expression after EVALUATE in a calculated table.
This is nice. How would you implement this in a Power BI model?
We published an example in this article:
www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/
Copy from Generate down to the final ) after Result and paste into BI as a calculated table, not sure how you add in the order by bit though
Brilliant!
Awesome!
This might sound like a very stupid question, but where do we use such a query if a measure can't return a table ? The only use I can see for this is to create a table and that wouldn't really make it interactive to other filters.
I'd appreciate if someone explains use-cases of such a query or how we can insert it into a table visualization.
We have an article describing that use case: www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/
We will also publish a newer article and video later with a better technique (not ready yet).
The technique shown in this video serves two purposes:
1) Create a report in other environments, such as Paginated Reports and Excel (you can dump the result of a DAX query in an Excel table).
2) Provide a longer example about how to write DAX code, dividing a problem in smaller simpler steps that are easier to write and test.
Aways Amazing !!!
brilliant!
Hi I have a query for you
I have five tables in my data model if I change the existing relation using userelationship function on one connection, How can I get the child data from the other tables using the new connection? It is an interview question
Hi Alberto! Can you point us to a web page or video that explains how SUMMARIZE works? All of the ones I find just show the depricated version that addes named expressions. I can't find anythat explains what is returned using just TABLE and GROUPBY. Does it just sum up the other columns? Concatenate strings? What about dates? Thank you!
Read these articles (just use the first part of SUMMARIZE without the aggregated columns):
www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/?aff=yt
www.sqlbi.com/articles/all-the-secrets-of-summarize/?aff=yt
too good
if you have only 2 products in 2009 the rank will show 1,2,4 (for others) wich would look weird :P (i know it's a Big what if only for educational purpose ... :) :D )
Good point!
Hi Alberto. Thanks for this great material. One question I'd have... If I want to return the result but without the auxiliary column that enables me to obtain the correct sorting order, would I have to wrap this result in SELECTCOLUMNS? Would that be enough to keep the sorting order? Thanks.
The column used for the sort order must be part of the query (SQL is different on this regard). A workaround for a limited number of rows is using hidden characters like in this article: www.sqlbi.com/articles/handling-customers-with-the-same-name-in-power-bi/
But you can hardly get this working with large datasets.
@@SQLBI Thanks for the quick reply. Do you think it would be hard for M$ to implement the possibility of returning a sorted set without the sorting column?
I think they don't have a compelling reason to do that - you should ask it first, and when they receive enough requests they could evaluate that.
This was great article. I was wondering why you used the @ColumnName for the column name since it also could used as a parameter. Are there any newer articles that demonstrates how to use it as a parameter that parallels the use of parameters in SQL Server. (www.sqlbi.com/blog/marco/2012/01/05/use-parameters-in-your-dax-queries/) is the latest article that I could find.
The @parameter syntax is not a parameter if used as column name, such as [@ColumnName].
For example, in this code only @Limit and @Name are parameters:
EVALUATE
FILTER (
CALCULATETABLE (
SELECTCOLUMNS ( 'Product', "@Name", 'Product'[Product Name] ),
'Product'[Color] = @ColorName
),
[@Name] > @Limit
)
However, it is clear that in a query where you have both parameters and temporary columns, you should evaluate a different naming convention for the column names. The reasons why [@ColumnName] could be a good naming convention (when you don't have parameters) is described here:
www.sqlbi.com/articles/naming-temporary-columns-in-dax/
awesome
Need your input if you want to get 3 topn from a list where values are similar. Like 100,10,10,10 as for me it is return 4 values instead of 3.
You must add some other element in the sort order - unfortunately TOPN only gets one measure, so you have to figure out an algorithm that uses a second column to make a final sort order (which could be hard, we know).
Uau....what is this?????.....amazing!!!!!
I haven't managed a single example from your tutorials so far, when I aggregate according to my turnover I get an error message that (that there is no match) with the other example at Summarize I couldn't even type it out 1:1.
Your examples are too worthless or not complex enough. Maybe you could explain what kind of data you are using and what kind of error handling would be possible.
Champion
can somebody please explain me how to sort by multiple columns in PBI Desktop as made by the last code line? - If we are in the situation where we have the same product for different @POS values, this can't be done just by using "sort by column" command. - this only if we don't wanna use @POS into our matrix/table, and if we can't use the index column of power query. Thanks
see these:
www.sqlbi.com/articles/handling-customers-with-the-same-name-in-power-bi/
www.sqlbi.com/articles/sorting-duplicated-names-in-a-level-of-a-hierarchy-with-dax/
@@SQLBI exactly what I needed. Thanks. Regards
Cool
a volte, presa dall'esaperazione, non so se benedirvi o maledirvi........................
Thought I knew DAX. Now I see... I know nothing at all