DAX Fridays!

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

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

50% 1

In today's #daxfridays we will cover the DAX function SUMMARIZECOLUMNS and compare it to SUMMARIZE.
Get Northwind Dataset: • Northwind data source:...
Link to DAX Fridays survey: bit.ly/2MMM4KK
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/product/sponsor-me
Many thanks in advance!Here you can download all the pbix files: curbal.com/donwload-center
▲▲▲▲▲▲▲▲▲▲
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#daxfridays #curbal #SUBSCRIBE

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

3 авг 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@workstuff5253 3 месяца назад

I love your excitement and energy in your videos 👍

@danielsiffredi8403 4 года назад

Ruth, you are an angel! you just taught me how to get done something that I was munching for DAYS... thank you SO MUCH!

@CurbalEN 4 года назад

Music to my ears! :) /Ruth

@joseagundis1 4 года назад

Gracias Ruth, como siempre EXCELENTES tus videos, además eres de los pocos MVP que leen y responden los comentarios, un plus más. Saludos

@CurbalEN 4 года назад

Todo un placer! Y charlar con vosotros es lo mejor de todo esto :) /Ruth

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

I love your comment about Microsoft documentation and 'words i cannot understand'. I thought it was just me! Great videos, keep them coming :)

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

You are not alone, trust me!

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

Best practices videos are always welcome, thanks and I'll be waiting for the next Friday vid!

@CurbalEN 4 года назад

Happy Friday! :) /Ruth

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

Thanks for the wonderful explanation on summurazecolumns using several examples. Really appreciate.

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

Ditto Ruth. Have logged these in my " Must Use" list! Thanks a lot. Have a great weekend yourself.

@CurbalEN 4 года назад

They are very useful :) /Ruth

@samuelmanseau6905 4 года назад

It's always a chance to find a way to improve! Thanks & thanks for your enthousiasme !

@CurbalEN 4 года назад

:) /Ruth

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

I don't know why I don't use this measure more often!!! you're the gratest!!!

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

Thanks!

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

Excellent content. Just used it for a Microsoft Customer Voice report to be able to filter responses when the filter content is one of the questions in a survey: Created a table from the DAX and added it into the data model. Took a bit of thinking to figure it out and test. Code below shows how to use the FILTER and RELATED functions. Activities = SUMMARIZECOLUMNS( msfp_questionresponse[msfp_surveyresponseid], msfp_questionresponse[Response], FILTER(msfp_questionresponse,RELATED(msfp_questions[Question]) = "Client") )

@dasrotrad 4 года назад

I have seen the magic! Summarize and Summarizecolumns .. totally kill it! I'm putting this to use today. You rock Ruth. Thank you!!

@CurbalEN 4 года назад

Yey!! Music to my ears and Happy Friday! /Ruth

@jacquelinelacan 4 года назад

Ruth! First of all, thank you for these top quality videos! But, I insist on saying that you're lovely! Tack så mycket!

@juanignacioXI 4 года назад

You are Awesome RUTH!!!!!

@CurbalEN 4 года назад

😊😊😊 /Ruth

@ayobamiologun9184 4 года назад

Another interesting exposition from you... Thank you Ruth.

@CurbalEN 4 года назад

Thanks Ayobami and have a great weekend! /Ruth

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

Ruth, fantastic work...thank a lot for your explanation...master of the DAX..

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

Surviving DAX is more appropriate 😂

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

Excellent explanation! Thank you.

@marcobaquero6867 4 года назад

Hi Ruth so clear, as always...!!! Tks!!

@CurbalEN 4 года назад

My pleasure Marco :) /Ruth

@AbhishekAbhi-mc1dv 3 года назад

Wow thanks a lot for the explanations

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

That little dance from 3:33 to 3:38 was cute :-). Always great to have an opportunity to enjoy what one does as work. That said, thanks for all your useful videos all these years!

@jonatanasencio4750 4 года назад

Amazing..That´s what I needed!!..Genius!!

@JohnnyTwoFingers 4 года назад

Ruth, some day can you do a video that does a higher level comparison of ALL the table functions, compare and contrast, and talk about what you might use each one for? This area is the most confusing part of DAX to me!!

@CurbalEN 4 года назад

Great idea! It will take me time to put it together though, but it is on my list now ! /Ruth

@juansepowerplatform 4 года назад

Excellent explanation, thank you.

@CurbalEN 4 года назад

Thanks Juan! /Ruth

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

Very nice explanation 👌

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

Thanks!

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

You helped me a lot with this video. 🙏

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

Music to my ears 😊

@vida1719 4 года назад

What a great explanation!

@CurbalEN 4 года назад

Thanks Vida! /Ruth

@gyorgybalogh3194 4 года назад

Excellent! Thank you!

@CurbalEN 4 года назад

Enjoy your weekend:) /Ruth

@Henrik.Vestergaard 3 года назад

Hi Ruth. At 8:00 you show the use of SUMMARIZECOLUMNS in measures. I can make it work in a simple measure, but what do you make of this from sqlbi: "SUMMARIZECOLUMNS cannot be used if the filter context contains a row context or a filter context generated by context transition or an arbitrarily shaped set. This limitation makes it unusable in any measure. It is a function intended solely for queries and calculated tables." Regards, Henrik

@alfredsfutterkiste7534 4 года назад

I did hear the wind or whatever it was but the video is still great!

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

Thanks for bring this topic Ruth. Excellent! I wonder, by using FILTER and SUMMARIZECOLUMNS, does it will reflect to expanded table or not (during calculation inside PBI)? We know that SUMMARIZECOLUMNS would fetch column(s) from particular table columns which those original table(s) have relationship between one another. I am glad if it's not.

@davidcadman3562 4 года назад

Great stuff. I use it all the time to query our Analysis Services model. It's much easier because we have multiple fact tables and often need to include measures aggregated from more than one of them. Summarize (and with Addcolumns) does include rows where the measure / all measures are blank.

@CurbalEN 4 года назад

Thanks for sharing and happy Fridays! /Ruth

@davidcadman3562 4 года назад

@@CurbalEN You too! QQ - I usually do it as 1. columns to group by, 2. one or more CALCULATETABLE and 3. the measures. Is there any real difference in using FILTER as part 2 as you did? More flexibility I guess in that criteria can be other than a True/False result. Do you know if there is any performance benefit with Filter over CalculateTable?

@jevgenijskaktins1066 4 года назад

No construction works but very nice Swedish melody appears in your English ;)

@marcosjosetiburciorodrigue3369 4 года назад

Ruth you rock!

@CurbalEN 4 года назад

🥳🥳 /Ruth

@jasonwoodward5501 4 года назад

Thanks again

@CurbalEN 4 года назад

🎉🎉 /Ruth

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

you are amazing

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

You too!!

@1yyymmmddd 4 года назад

Simple and easy explanation. Good job ! However beware that ADDCOLUMNS(SUMMARIZE ...) scenario works much much faster.

@CurbalEN 4 года назад

Yey!! Happy Friday! :) /Ruth

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

Hello Ruth, Thank you for your presentation. I have used it and found working very well. Just one element, I placed it in card it is working. But, when I put it in matrix with Year and month parameters, it is showing error. I wonder what additional steps I need to take in order to show it in matrix. Please find my dax as follows. Summarize Invoice Total by Suborder ID and Invoice Date = COUNTROWS( FILTER( SUMMARIZECOLUMNS( 'DBiCareBillingOrderLevel'[Suborder ID], 'DBiCareBillingOrderLevel'[Invoice Date], "SumOfRevenue",[Total_Invoice $] ), [SumOfRevenue] > 0 ) ) Appreciate if you could provide some insight or refer to any of your other video. Regards Joy

@brunof.s.8186 4 года назад

last week was ADDCOLUMNS , today is SUMMARIZECOLUMNS...the revenge of the forgotten dax measures on dax fridays! love them both, so useful! it opens so many possibilities. thank you! unrelated PS : tomorrow is the day that the legend ELIUD KIPCHOGE will attempt the sub 2h marathon in Vienna. stay tune!! happy friday ruth!

@CurbalEN 4 года назад

I Will check on the internet! How is your training going? I have yet to decide if I will go for it next year, but training anyways :) Happy Friday!! /Ruth

@brunof.s.8186 4 года назад

@@CurbalEN i've just completed a HM two weeks ago, so right now i'm in a "off season", doing more cross-training than actually running, but i will resume next month in order to prepare for sao paulo marathon. (april-2020) what's keeping you ?

@CurbalEN 4 года назад

That is a great question... afraid to commit to one more thing. It takes dedication to make it and time that I would need to take from other things... Though call :( How do you make it a priority? What is your secret?

@brunof.s.8186 4 года назад

@@CurbalEN i guess i just have created the routine. exercising is as important as work, sleep well, proper eating, etc. we just have to do it. so, squeezing this into a marathon program is not that far-fetched. and i really enjoy the sunday's long runs. but of course, one's agenda it's totally different from other. mostly of the time mine is manageable and predictable , so i guess is "easier" for me. i've read numerous race reports on reddit from people who trains at 4:00 AM everyday, having two or three kids to taking care of so... i guess there is always a way. but as they say, there are marathons every year. maybe you'll find a better time to that commitment. but by then, you'll have > 100k subscribers in your channel, so will never be easy ! =P

@CurbalEN 4 года назад

You are so right! I do exercise often but short time to manage everything else and in the summer is golf in the weekends. Now that the cold weather is here is when I really start thinking about longer runs in the weekend. It’s not decided yet, and not too late. Let’s see. Thanks for your thoughts though!

@dex461 Год назад

Great video! Well explained. I have question though, and I'm not sure the best way to accomplish this, but I have data set that contains a series of categorical information in a in a column (vertically), and the associated date with those categories displayed within a row (horizontally). Is there a way to pull the associated values in both those arrays and perform calculations, without having them vertically aligned? Let me know if this makes sense. Thank you!

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

Is it possible to pivot the columns in a summarize table? For example, you want to show Category Names as Columns and sumofsales as the value on each row under the columns?

@jayantmishra4270 Год назад

Hi... Can you help me to solve this question ?? Which of the following DAX function will return a table :- 1 SUMMARIZECOLUMNS 2 CALCULATETABL 3 RELATEDTABLE 4 All of the above 5 None of the above Please answer the question??

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

On summarizeColums, Why didn't you use an aggregate function to sum sales by year and Category?

@AkshayKumar-vd5wn 2 года назад

What of you have 2 fact tables does SSUMMARISECOLUMNS know which one to pick? Can we specify which fact table bto summarize for SYMMARIZECOLUMNS.

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

Hi curbal, how can i get a pivot or matrix like view in this dax output. Where i can see category revenue for each year in a seperate column. Instead all years in one column.

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

Hi Ruth! I ended here looking to make virtual tables (that I believe are made by storage engine, right? ) so my visuals Load faster when making rolling avarage on line visuals, and cumulative totals on table visuals. Do yo have a video por that?

@NaraMeerammaTrust 4 года назад

Ruth, Great video and very useful topic. I would like to know are the relationships required among tables on the modelling in order to create SUMMARIZECOLUMNS Table. I guess it is not required to maintain a physical relationship explicitly on modelling tab for SUMMARIZECOLUMNS . Can you please clarify on this point too?

@CurbalEN 4 года назад

Try removing the relationships from my demo file to see what happens! ;) Happy Friday! /Ruth

@NaraMeerammaTrust 4 года назад

@@CurbalEN The file is not available yet at download Center. Once it is loaded i will try this example without having relationships. I have implemented this already in my current project. I wanted to double check and make sure if it works in all situations. Thank you Ruth. Please let me know if the file is uploaded to download center.

@JohnnyTwoFingers 4 года назад

I think you can instead just use a filter expression to simulate a relationship, and you can do multiple columns then also....right???

@CurbalEN 4 года назад

It is on now , sorry about that :)

@hi_vishy Год назад

How to refer to a column like Product or Country from summarizecolumn in Values() or such similar function? I want to use these values as filter in another DAX

@tucabelotti 4 года назад

Fantastic job, as always. Just one observation: when you used SUMMARIZECOLUMNS you said that the result was exactly the same but this is because you didn't have any blank row in your table, right? At least this is what I understood. I know you posted last week the way to deal with it using IGNORE. I wonder which is the best practice. Use the SUMMARIZECOLUMNS + IGNORE or ADDCOLUMN + SUMMARIZE? By the way, I am a huge fan!!!!!!!

@CurbalEN 4 года назад

Hi Tuca! You are 100% right :) According to sqlbi it is better to use summarizecolumns but I would always test in your model and compare actual performance. There are so many factors that can affect performance... Have a great Sunday! /Ruth

@NaraMeerammaTrust 4 года назад

Trevor, can you please make it bit clear? If possible can you please give some example for your view.

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

So say I am creating a control chart. I have the UCL and the LCL that are derived using the standard deviatio.ln. I have a slicer that inputs the multiplier to the standard deviation so from 1 to 3 and with it I control the UCL and LCL. I want to create card with a count of number of data points that are outside the control limits when I change the slicer inputs. I think the way you can do it using virtual tables and countrows ... However I am failing to do so ... If you can please help

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

Hi mam When I try to save the report in PowerBI desktop it is showing unable to save document cannot open the package because file mode is not valid for the stream Can you please help me in this mam

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

What is data lineage in power bi?

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

what is difference between summarize and summarizecolumns in power bi

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

I have a video on that, search in the channel

@alfredsfutterkiste7534 4 года назад

I struggle to see where all the table/querying functions might be useful in DAX in a real business context?

@CurbalEN 4 года назад

Great question, it is great for creating virtual tables on the fly and do calculations on them. Deservs a video!

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

hi your help center includes only up to number 97 pbix files rest is missing can you help thanks

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

Just click to see all pages at the bottom on the page

@suzannegriswold7555 4 года назад

You are such a good teacher. Just watched another one by another teacher and he just talked too much...tried to put too much in single video. Completely lost me

@CurbalEN 4 года назад

Thanks for the feedback :) /Ruth

@lgnh6430 11 месяцев назад

Absoluut so misleading

@chengwang411 Год назад

Hey, I remember you once did a video about a trick that you can somehow select multiple field names in Dax together for an easy mass update. Can you gimme a link ? I can’t find it 🥲 tq