Writing a DAX query using TOPN

Подписаться 103 тыс.

Просмотров 34 тыс.

50% 1

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...

Наука

Опубликовано:

2 ноя 2020

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 91

@eldrigeampong8573 2 месяца назад

Thanks a lot for continuously sharing your immense knowledge with the BI community

@MrAszpic 3 года назад

This video is the answer of thousands of questions on the official Power BI Community. Thanks a lot for sharing!

@Drivenbyfate1 3 года назад

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.

@Mrglasshalfempty 3 года назад

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!

@HachiAdachi 3 года назад

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!

@zeisslaimen1 3 года назад

OMG Alberto you are truly a DAX Ferrari:) fantastic!

@CJ-jc8tn 3 года назад

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.

@yiubili1 3 года назад

Amazing video, i am happy that youtube recommended this to me.

@EricaDyson 3 года назад

Wonderful. Your explanations are so clear and it seems so simple when YOU do it! Thanks so much.

@sasikr Год назад

Thank u so much. BI community is indebted to you

@smartdata678 3 года назад

I love the way you explain the topic!!! Greetings from Poland. :)

@Chris_in_fremantle Год назад

A masterful demonstration.

@fernandotorres4463 3 года назад

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.

@jimayas 3 года назад

Stellar stuff Alberto! Thank you for sharing your knowledge!

@lilychiao70 3 года назад

Oh, my god, I just realized who you are! So glad to watch these excellent videos. Thanks.

@davidbrown2204 3 года назад

Simply superb, well done Alberto.

@2404Pepe 2 года назад

Third time I watch this video and finally got it !!!! thanks so so much for your dedication Alberto!!!

@avsthoughts-aneeshvs4422 3 года назад

Clear step by step approach explained in a simple way.....👍👍👍

@pedrosanteloy 3 года назад

Amazing! Really really useful! Thanks for sharing this content.

@lokesh2635 3 года назад

I may take my entire life to write dis kind of codes 😅😃😃

@emilyamalkin4367 Год назад

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. ❤️

@alt-enter237 3 года назад

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!)

@jaikumardaiya4503 3 года назад

Great DAX Video i have ever watched.

@michaeldiluzio1944 2 года назад

Amazing video to watch, thank you!

@3danim8r1 2 года назад

Just WOW...you are outstanding Sir. Thank you so much for sharing.

@georgekaroubas6136 3 года назад

Excellent presentation. Thank you

@ringovski1980 3 года назад

Great video, very clearly explained.

@mwaltercpa 3 года назад

Very helpful. Thank you!

@Milhouse77BS 3 года назад

Wow! Next level DAX.

@natibelay6157 2 года назад

Perfect example.

@diegoj9056 3 года назад

Great video, thanks

@markusleucht6603 Год назад

Excellent!

@jayeshsuthar9198 2 года назад

Great example

@PowerBITrujillo 3 года назад

extraordinary!

@ITSNev 3 года назад

Thanks, very good...

@picious 3 года назад

Aways Amazing !!!

@DanielADamico 3 года назад

Brilliant!

@BoyClassicall 3 года назад

Code clean as a whistle .

@sue_bayes 3 года назад

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?

@amarines2 3 года назад

Execelente explicación

@andreibulatov2395 3 года назад

Awesome!

@Gustavofuentesm 3 года назад

brilliant!

@StoneZhong 3 года назад

Good video, I would like to figure that with Spark SQL's Window function, we can get things done as well.

@pawewrona9749 3 года назад

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!

@SQLBI 3 года назад

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/

@diegomex85 3 года назад

awesome

@Bharath_PBI 3 года назад

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 👍

@obazeeghosa 3 года назад

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

@user-yv2fw3cb8p 3 года назад

too good

@RandomlyWisdom 2 года назад

How many years I will need to become an expert like Alberto?

@ed2921 3 года назад

Champion

@joaquimcosta952 3 года назад

Uau....what is this?????.....amazing!!!!!

@danjarupath 3 года назад

Cool

@PeterKontogeorgis 3 года назад

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?

@SQLBI 3 года назад

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/

@HarishS12137 3 года назад

How do I become good at Dax? This is so inspiring...

@vladsamoilov9905 3 года назад

Read Alberto and Marco's books, watch videos and takes their courses. Best investment you can make

@nagendrakg5062 3 года назад

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

@SQLBI 3 года назад

The video shows how to create a table expression, you can use the same expression after EVALUATE in a calculated table.

@venkataramanareddybareddy7077 3 года назад

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?

@SQLBI 3 года назад

Use this: www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/

@chilliyou 3 года назад

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.

@SQLBI 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).

@blaisehara8337 2 года назад

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.

@SQLBI 2 года назад

See this article+video: www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/

@rick_. 3 года назад

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!

@SQLBI 3 года назад

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

@Taastrupbaby 3 года назад

This is nice. How would you implement this in a Power BI model?

@SQLBI 3 года назад

We published an example in this article: www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/

@sue_bayes 3 года назад

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

@dariuszspiewak5624 3 года назад

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.

@SQLBI 3 года назад

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.

@dariuszspiewak5624 3 года назад

@@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?

@SQLBI 3 года назад

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.

@chandrapamidikalva3277 3 года назад

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

@IoriYagamiKOF98 3 года назад

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 )

@SQLBI 3 года назад

Good point!

@yazanhlayel6983 3 года назад

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.

@SQLBI 3 года назад

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.

@ph3ll3r 3 года назад

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.

@SQLBI 3 года назад

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/

@vittoriogiatti 3 года назад

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

@SQLBI 3 года назад

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/

@vittoriogiatti 3 года назад

@@SQLBI exactly what I needed. Thanks. Regards

@David-kf8qo Год назад

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.