Great video! I love the way you disintegrate different parts and explain them in simple language. I was already a big fan of your voice and way of your speech delivery but after seeing you in the video for the first time I really love your simplicity. All your videos are very informative and you simplify all difficult functions so easily that is really appreciative. Thank you very much.
Curbal I really like the statement you wrote on your website. "Feel free to contact us the old fashioned way. Sometimes it’s nice to talk to a real person." I am looking forward to contact you one on one. I am texting you on hangouts hope will get reply. :)
I use MySQL and postgresql. I think currentgroup and it use its not intuitive at all. But your video is the best in order to explain this. Congratulations! It help me.
Great video, impressive your ability to make difficult things simple. By the way, it was great to have learned the COMBINEVALUES function with one of your videos.
Hello Ruth how are you? I'm struggling because i don't want to do a SUMX or an AverageX aggrupation function, i'd like to use a FirstNonBlank and LastNonBlank... is there a way to groupby and there have the First & last values?
Hello Ruth, Hope you are absolutely good. I have a query, so if you can make time then only reply otherwise not. The PBIX file you provided in the link in the description of this video, has A Measure called 'Total Sales', which is made of this function : Total Sales = SUM(Order_Details[Tot sales]).. I am not able to find this 'Tot Sales', what it is? It would be kind enough if you tell. Warm regards,
Hi, is there any chance to have video on olap connection with power bi? I have seen lots of issues on the net but there is hardly any video which exactly explain steps by steps for the connectivity?
Curbal just to add what I had wrote above, “Guy in a Cube” tried to explain but somehow there is a missing link which I not able to figure it out. Your help will be highly appreciated. I was able to connect through Power BI Service but not through Power BI desktop.
Not sure what example you have mind, but generally, yes, you can group without summarize. Post an example of what you are trying to do in the power bi community to get specific help. /Ruth
Curbal I am trying to create regions. Example: column with various names. ncal = name 1,2,3,4 Scal = name 1,2,3,4 the user can select ncal or scal I am trying to create pivot slicer filter. Best way I understand what I am trying to do
I have a query in power bi and the table what I'm trying to group or summarize (but in the same table) has various columns. Store, Style Key, Style Color, Sales Qty, Stock Qty. We can find more than one Style Key in various Colors, it looks so: Madrid Store / 123456 / Black / 1 / 38 Madrid Store / 123456 / Grey / 2 / 58 Madrid Store / 123456 / White / 1 / 29 Madrid Store / 125344 / Black / 1 / 23 Madrid Store / 125344 / White / 3 / 43 What I need to group or summarize is: Madrid Store / 123456 / Black / 1 / 38 / 4 / 125 Madrid Store / 123456 / Grey / 2 / 58 / 4 / 125 Madrid Store / 123456 / White / 1 / 29 / 4 / 125 Madrid Store / 125344 / Black / 1 / 23 / 3 / 66 Madrid Store / 125344 / White / 2 / 43 / 3 / 66 The goal is to take away the color, and just keep as MASTER the Style Key. Your video is very clear if you will create a separate table, but how can I make the same in the same original table. Thank you so much.
I want to create a virtual table(Grouped table) with DAX, after that i want to calculate max value in grouped virtual table. Is that possible ? How can i use GROUP BY function with purpose of this OR it can be used ?
Thanks for this informative video. Can one get the max sales by country and the corresponding product name also? Because in your scenario, it would be quite useful if one knows what product the max sales was for.
But if we add the product in the last Group by, then the same table as in the first Group by is returned. That is, it lists all the products for each country with their amount and not the max amount and the corresponding product only. Highest Sale by Country = VAR Sales = GROUPBY(Order_Details,Customers[Country],Products[ProductName],"Total Sales",SUMX(CURRENTGROUP(),Order_Details[Quantity] * Order_Details[UnitPrice])) RETURN GROUPBY(Sales,Customers[Country],Products[ProductName],"Max Sales", MAXX(CURRENTGROUP(),[Total Sales])) Am I missing something here?
I mean just like this: HighestSalesBYCountryByProduct = GROUPBY(Order_Details,Customers[Country],Products[ProductName], "Total Sales", MAXX(CURRENTGROUP(),Order_Details[Quantity]*Order_Details[UnitPrice])) /Ruth
the data in the new table not matched to the actual data. I have the main table of parts and all the related details of the parts, with failures dates and failure reason and location of the part. i want to get group by table will see only the part number and the number of failure for specific part but the group by table gives me wrong number for some reason.
Group by returns a table, but you can feed it to another measure and it will return a value. Post the details in the power bi community to get detailed help. /Ruth
Thanks Ruth for the such an informative video. Could you please make a video on how to create bucketing / bin (example - Amount 1000, >2000 and >5000 etc) in Power Bi which works dynamically using various filters of data set. I had created one but its not dynamic. Pls help me. Thanks in advance.
Just wanted to highlight this point from the Microsoft syntax notes - "The CALCULATE function (and therefore measures) are not allowed in the expression." I guess that's because it's going directly to the storage engine? Surprising to me...
Awesome! Thank you... it isn't clear why the total sales in the 'SalesByCountryByProduct' visual (1,354,458.59 when not filtered by 'Customers-Country' filter) does not equate to the Total Sales from the core model (1,265,793.04) .... bearing in mind the two tables created in this exercise are standalone with no relationship to the core data model, yet are derived from data of the core model ....
Thanks Ruth for your time to reply... yeah, previously watched this Totals video of yours (another helpful video I might add), thinking it had to do with filter context; but it doesn't appear so... (on the other hand, relatively new to BI and know enough to be dangerous).... when exporting the 'SalesByCountryByProduct' visual to Excel (essentially a data dump of the table), the detail records/rows equal to the 1,354,458.59; no filtering taking place ... so something is seemingly taking place during the creation of this table with the GROUPBY or CURRENTGROUP functions.
Ruth, thank you for your hard work. You have helped me so much.
It is grateful seeing someone you respect smiling. You deserve the best of world.
Thanks and you too! 😊
Best luck in the summit! You don't know how much I appreciate all your hard work Ruth. Muchas gracias guapa!
Todo un placer Danilo y feliz fin de semana. A disfrutarlo!
/Ruth
Ruth, you are the best teacher on YT, your channel is amazing.🤩 Thank you so much for all your hard work, you have helped me to learn so much... 😊
And thanks for all the continued support!! 💛
Great video!
I love the way you disintegrate different parts and explain them in simple language.
I was already a big fan of your voice and way of your speech delivery but after seeing you in the video for the first time I really love your simplicity.
All your videos are very informative and you simplify all difficult functions so easily that is really appreciative.
Thank you very much.
Many thanks for the detailed feedback, and really glad my videos are helpful :)
Have a fantastic Sunday!
/Ruth
Curbal thanks you too have great Sunday. Is there any means to contact you?
Here: curbal.com/contact
/Ruth
Curbal I really like the statement you wrote on your website.
"Feel free to contact us the old fashioned way. Sometimes it’s nice to talk to a real person."
I am looking forward to contact you one on one. I am texting you on hangouts hope will get reply. :)
:)
Thank you Ruth, for sharing the information.
Looking for more useful hidden formula
Gracias Ruth!! realmente ignoraba la existencia de esta funcion, claramente explicado como siempre!!
Perfecto, aprovecha a celebrarlo que es Viernes!
/Ruth
Another great tutorial. Worked a treat for some a UK population trend dashboard that I'm building.
You are right, very useful function. Nice detailed explanation (once again). Thank you and warm greatings from The Netherlands.
Is it warm there? Here we at 4C ... in mid April! 😵
Happy Friday and thanks for the feedback :)
/Ruth
To Dutch standards yes. This weekend will be like 19°C and after tuesday it will be 20°C +. So we say goodbye winter and hello spring. 😎
I need to migrate to lower latitudes....
Enjoy!
/Ruth
LOL Well you can still take a plane to start with this weekend. :-)
Hahah one way ticket? 😂😂
/Ruth
useful information. Thanks Ruth..
My pleasure , have a great weekend Ankush!
/Ruth
Great explanation of the function Ruth! DAX is just a little different than tsql obviously and struggled applying group by until your video.
Fantastic! Then it is mission accomplished :)
Have a great Friday!
/Ruth
I use MySQL and postgresql. I think currentgroup and it use its not intuitive at all. But your video is the best in order to explain this. Congratulations! It help me.
Great video, impressive your ability to make difficult things simple. By the way, it was great to have learned the COMBINEVALUES function with one of your videos.
Thanks! My mum is a teacher, maybe I got her gene? ;)
Have a nice weekend!
/Ruth
Hello Ruth how are you?
I'm struggling because i don't want to do a SUMX or an AverageX aggrupation function, i'd like to use a FirstNonBlank and LastNonBlank... is there a way to groupby and there have the First & last values?
Hello Ma'am
How to do group by two coloumns which are from two different tables. Please let me know the dax
Why my excel 365 crashes when running power query after .net framework shows up below. TIA.
Hello Ruth,
Hope you are absolutely good. I have a query, so if you can make time then only reply otherwise not. The PBIX file you provided in the link in the description of this video, has A Measure called 'Total Sales', which is made of this function : Total Sales = SUM(Order_Details[Tot sales])..
I am not able to find this 'Tot Sales', what it is? It would be kind enough if you tell.
Warm regards,
It is probably hidden, have you check that?
/Ruth
Not yet. But I will.
It should be that!
/Ruth
Is it better tondo group by in power query for performance
Hi, is there any chance to have video on olap connection with power bi? I have seen lots of issues on the net but there is hardly any video which exactly explain steps by steps for the connectivity?
Absolutely, thanks!
One thing though... do you know of any public source I can connect to?
/Ruth
Curbal just to add what I had wrote above, “Guy in a Cube” tried to explain but somehow there is a missing link which I not able to figure it out. Your help will be highly appreciated. I was able to connect through Power BI Service but not through Power BI desktop.
Hi Adil,
Do you have a link to the video?
/Ruth
Interesting function, thank you for sharing
You welcome Mehdi!
/Ruth
nice video. I have a doubt. Can we groupby upto a particular date (selected date on a before slicer)?
Hello Ruth,
Hope you are absolutely good. I have a query regarding DAX can you help me out.
Question
Is there a way to group items in a column that are not the same. I DNT want to sum them just group them together
Not sure what example you have mind, but generally, yes, you can group without summarize.
Post an example of what you are trying to do in the power bi community to get specific help.
/Ruth
Curbal
I am trying to create regions.
Example: column with various names.
ncal = name 1,2,3,4
Scal = name 1,2,3,4
the user can select ncal or scal
I am trying to create pivot slicer filter. Best way I understand what I am trying to do
Hello Ruth,
But, what can I do when I need to groupby or summarize in the same original table in an one or two addcolumns?
Cant you do it in Power Query?
/Ruth
I have a query in power bi and the table what I'm trying to group or summarize (but in the same table) has various columns. Store, Style Key, Style Color, Sales Qty, Stock Qty. We can find more than one Style Key in various Colors, it looks so:
Madrid Store / 123456 / Black / 1 / 38
Madrid Store / 123456 / Grey / 2 / 58
Madrid Store / 123456 / White / 1 / 29
Madrid Store / 125344 / Black / 1 / 23
Madrid Store / 125344 / White / 3 / 43
What I need to group or summarize is:
Madrid Store / 123456 / Black / 1 / 38 / 4 / 125
Madrid Store / 123456 / Grey / 2 / 58 / 4 / 125
Madrid Store / 123456 / White / 1 / 29 / 4 / 125
Madrid Store / 125344 / Black / 1 / 23 / 3 / 66
Madrid Store / 125344 / White / 2 / 43 / 3 / 66
The goal is to take away the color, and just keep as MASTER the Style Key.
Your video is very clear if you will create a separate table, but how can I make the same in the same original table.
Thank you so much.
Would you mind posting in the power bi community? You will get help in no time!
/Ruth
I will do.
What's the difference between doing this in power query and dax? Also, is it possible to do this in excel instead of power bi?
Thanks Ruth
I want to create a virtual table(Grouped table) with DAX, after that i want to calculate max value in grouped virtual table. Is that possible ?
How can i use GROUP BY function with purpose of this OR it can be used ?
Thanks for this informative video.
Can one get the max sales by country and the corresponding product name also? Because in your scenario, it would be quite useful if one knows what product the max sales was for.
Yes, if you add product name to the last group by, you should get just that :)
Have a nice evening!
/Ruth
But if we add the product in the last Group by, then the same table as in the first Group by is returned. That is, it lists all the products for each country with their amount and not the max amount and the corresponding product only.
Highest Sale by Country =
VAR
Sales = GROUPBY(Order_Details,Customers[Country],Products[ProductName],"Total Sales",SUMX(CURRENTGROUP(),Order_Details[Quantity] * Order_Details[UnitPrice]))
RETURN
GROUPBY(Sales,Customers[Country],Products[ProductName],"Max Sales", MAXX(CURRENTGROUP(),[Total Sales]))
Am I missing something here?
I mean just like this:
HighestSalesBYCountryByProduct = GROUPBY(Order_Details,Customers[Country],Products[ProductName],
"Total Sales", MAXX(CURRENTGROUP(),Order_Details[Quantity]*Order_Details[UnitPrice]))
/Ruth
@@sgk1966 Did it work for you Gopa Kumar? The solution given below doesnt seem to work.
I really enjoyed your explanation. Thank you.
thanks for a groupby function...thanks a lot :)
My pleasure and Happy Friday :)
/Ruth
Thanks again for your great Videos, as always. Im trying to use this one but find the data not accurate. any thought why?
Mmmm, What do you mean by accurate?
/Ruth
the data in the new table not matched to the actual data. I have the main table of parts and all the related details of the parts, with failures dates and failure reason and location of the part. i want to get group by table will see only the part number and the number of failure for specific part but the group by table gives me wrong number for some reason.
Ok, post in the power bi community explaining on detail what you are trying to do. The details are needed to troubleshoot the issue.
/Ruth
Thanks as always Ruth!
Not very useful this time, Maybe the next :)
/Ruth
Very interesting and useful video. Reminds Sql group by
Excellent! Have a great weekend :)
/Ruth
Thanks a lot. It was of great help 💕
great tip ruth
Thanks Alexis and happy Friday !
/Ruth
Thanks a ton :) Life saver as always :)
Hi, can we use it without making new table? Group by or summerize just in column in first table?
Group by returns a table, but you can feed it to another measure and it will return a value.
Post the details in the power bi community to get detailed help.
/Ruth
"I know you love table functions"
You know me so well. haha
😂😂
/Ruth
Thanks Ruth for the such an informative video. Could you please make a video on how to create bucketing / bin (example - Amount 1000, >2000 and >5000 etc) in Power Bi which works dynamically using various filters of data set. I had created one but its not dynamic. Pls help me. Thanks in advance.
Maybe this Will help you:
m.th-cam.com/video/SFWHDWiqXzI/w-d-xo.html
/Ruth
Ruth, I prefer your explanation than Marco's. Thanks again.
Thanks! It is great to have multiple sources to read and learn from :)
/Ruth
Just wanted to highlight this point from the Microsoft syntax notes - "The CALCULATE function (and therefore measures) are not allowed in the expression."
I guess that's because it's going directly to the storage engine? Surprising to me...
Thanks Reuben :)
/Ruth
Hello !! I loved it. Does the rollup and rollupgroup similar to this groupby ? Can you do a video on rollup ? Thank you !
Great Idea! I have to review those :)
/Ruth
@@CurbalEN thanks
Well done Ruth in providing another clear video. Move over Russo.
Hi Ruth I have watch all videos DAX please upload videos on dashboard in power by different examples
Videos on dashboard?
/Ruth
That's great! Thanks
Thanks to you for the feedback:)
And happy Friday!
/Ruth
Awesome! Thank you... it isn't clear why the total sales in the 'SalesByCountryByProduct' visual (1,354,458.59 when not filtered by 'Customers-Country' filter) does not equate to the Total Sales from the core model (1,265,793.04) .... bearing in mind the two tables created in this exercise are standalone with no relationship to the core data model, yet are derived from data of the core model ....
I don’t remember the particulars of that video, but totals are not what them seem to be:
th-cam.com/video/ufHOOLdi_jk/w-d-xo.html&vl=en
/Ruth
Thanks Ruth for your time to reply... yeah, previously watched this Totals video of yours (another helpful video I might add), thinking it had to do with filter context; but it doesn't appear so... (on the other hand, relatively new to BI and know enough to be dangerous).... when exporting the 'SalesByCountryByProduct' visual to Excel (essentially a data dump of the table), the detail records/rows equal to the 1,354,458.59; no filtering taking place ... so something is seemingly taking place during the creation of this table with the GROUPBY or CURRENTGROUP functions.
I am travelling at the moment so I can’t check, I will try to check it when I am back!
If you are in a hurry, check with the power bi community!
/Ruth
Thanks for the video
wow such an useful expression !!
It is :)
Probably SQL inspired!
/Ruth
Thanks!
You welcome!
/Ruth
INFORMATIVE
Thanks! 🙏
/Ruth
I love u
Calculated Columns ? Realy ? Its so ........... beginner . I´d like to see it all as measure .
For higher level content I recomend you sqlbi material. You will enjoy that.
No idea why there is total seles and then sumx