Writing a DAX query using TOPN
Vložit
- čas přidán 2. 11. 2020
- Learn how to write a DAX query using TOPN to display the first elements in individual rows, grouping the remaining elements in a single "Other" row. The goal of the video is to show how to write a DAX query from scratch, reaching the final goal in small steps.
The query shown in this video can be useful to build tables in Excel (not PivoTables), report in Paginated Reports, and calculated tables in Power BI.
You can test the code using DAX Studio connected to the Power BI file available in this article, which has also a different approach based on measures that work in Power BI reports: sql.bi/245527?aff=yt
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
This video is the answer of thousands of questions on the official Power BI Community. Thanks a lot for sharing!
Thanks a lot for continuously sharing your immense knowledge with the BI community
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!
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.
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.
Thank u so much. BI community is indebted to you
Wonderful. Your explanations are so clear and it seems so simple when YOU do it! Thanks so much.
OMG Alberto you are truly a DAX Ferrari:) fantastic!
I love the way you explain the topic!!!
Greetings from Poland. :)
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.
Perfect example.
I may take my entire life to write dis kind of codes 😅😃😃
Stellar stuff Alberto!
Thank you for sharing your knowledge!
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. ❤️
Third time I watch this video and finally got it !!!! thanks so so much for your dedication Alberto!!!
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!)
Simply superb, well done Alberto.
Clear step by step approach explained in a simple way.....👍👍👍
Amazing! Really really useful! Thanks for sharing this content.
A masterful demonstration.
Just WOW...you are outstanding Sir. Thank you so much for sharing.
Amazing video to watch, thank you!
Excellent presentation. Thank you
Great video, very clearly explained.
Great DAX Video i have ever watched.
Very helpful. Thank you!
Wow! Next level DAX.
Great example
Excellent!
Great video, thanks
Brilliant!
Aways Amazing !!!
extraordinary!
Thanks, very good...
Awesome!
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?
brilliant!
Execelente explicación
Code clean as a whistle .
Good video, I would like to figure that with Spark SQL's Window function, we can get things done as well.
awesome
too good
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 👍
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
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/
Champion
Cool
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).
How many years I will need to become an expert like Alberto?
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/
Uau....what is this?????.....amazing!!!!!
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.
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
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 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
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.
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
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
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/
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
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!
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/
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.
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.
a volte, presa dall'esaperazione, non so se benedirvi o maledirvi........................
Thought I knew DAX. Now I see... I know nothing at all