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") )
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
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!
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 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?
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.
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
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?
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??
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 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.
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
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
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
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!
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
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?
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
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.
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!!
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!!!!!!!
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
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!
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
@@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 ?
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?
@@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
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!
I love your excitement and energy in your videos 👍
Ruth, you are an angel! you just taught me how to get done something that I was munching for DAYS... thank you SO MUCH!
Music to my ears! :)
/Ruth
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")
)
Thanks for the wonderful explanation on summurazecolumns using several examples. Really appreciate.
I have seen the magic! Summarize and Summarizecolumns .. totally kill it! I'm putting this to use today. You rock Ruth. Thank you!!
Yey!! Music to my ears and Happy Friday!
/Ruth
Ruth! First of all, thank you for these top quality videos! But, I insist on saying that you're lovely! Tack så mycket!
You are Awesome RUTH!!!!!
😊😊😊
/Ruth
Best practices videos are always welcome, thanks and I'll be waiting for the next Friday vid!
Happy Friday! :)
/Ruth
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
It's always a chance to find a way to improve! Thanks & thanks for your enthousiasme !
:)
/Ruth
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!
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.
Thanks for sharing and happy Fridays!
/Ruth
@@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?
No construction works but very nice Swedish melody appears in your English ;)
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.
Hi Ruth so clear, as always...!!! Tks!!
My pleasure Marco :)
/Ruth
On summarizeColums, Why didn't you use an aggregate function to sum sales by year and Category?
What of you have 2 fact tables does SSUMMARISECOLUMNS know which one to pick?
Can we specify which fact table bto summarize for SYMMARIZECOLUMNS.
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
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?
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??
You helped me a lot with this video. 🙏
Music to my ears 😊
Excellent explanation, thank you.
Thanks Juan!
/Ruth
Simple and easy explanation. Good job ! However beware that ADDCOLUMNS(SUMMARIZE ...) scenario works much much faster.
Yey!! Happy Friday! :)
/Ruth
I did hear the wind or whatever it was but the video is still great!
What is data lineage in power bi?
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?
Try removing the relationships from my demo file to see what happens! ;)
Happy Friday!
/Ruth
@@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.
I think you can instead just use a filter expression to simulate a relationship, and you can do multiple columns then also....right???
It is on now , sorry about that :)
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
Ruth you rock!
🥳🥳
/Ruth
what is difference between summarize and summarizecolumns in power bi
I have a video on that, search in the channel
Trevor, can you please make it bit clear? If possible can you please give some example for your view.
Excellent! Thank you!
Enjoy your weekend:)
/Ruth
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
Thanks for the feedback :)
/Ruth
Absoluut so misleading
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
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!
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
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?
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
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.
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!!
Great idea!
It will take me time to put it together though, but it is on my list now !
/Ruth
Wow thanks a lot for the explanations
I don't know why I don't use this measure more often!!! you're the gratest!!!
Thanks!
hi your help center includes only up to number 97 pbix files rest is missing can you help thanks
Just click to see all pages at the bottom on the page
Very nice explanation 👌
Thanks!
you are amazing
You too!!
Amazing..That´s what I needed!!..Genius!!
Excellent explanation! Thank you.
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!!!!!!!
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
I love your comment about Microsoft documentation and 'words i cannot understand'. I thought it was just me! Great videos, keep them coming :)
You are not alone, trust me!
Ruth, fantastic work...thank a lot for your explanation...master of the DAX..
Surviving DAX is more appropriate 😂
I struggle to see where all the table/querying functions might be useful in DAX in a real business context?
Great question, it is great for creating virtual tables on the fly and do calculations on them. Deservs a video!
Another interesting exposition from you... Thank you Ruth.
Thanks Ayobami and have a great weekend!
/Ruth
Ditto Ruth. Have logged these in my " Must Use" list! Thanks a lot. Have a great weekend yourself.
They are very useful :)
/Ruth
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
Todo un placer! Y charlar con vosotros es lo mejor de todo esto :)
/Ruth
What a great explanation!
Thanks Vida!
/Ruth
Thanks again
🎉🎉
/Ruth
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!
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
@@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 ?
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?
@@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
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!