Configure Power BI Aggregations
Ғылым және технология
Learn to configure your Power BI Aggregations table to really increase the speed of your DAX queries. This does NOT require Power BI Premium!
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #Aggregations #GuyInACube
Пікірлер: 48
Great videos, and tremendous resource for a beginner like myself. Q: How do / can I create a aggregate column within the same table?
Great video! Loving this series on Aggs.
@GuyInACube
4 жыл бұрын
Thanks! 👊
Great video Patrick, thank you! Why is the average option not added in the calculation window? I'm averaging the values (measures) in my pivot table script.
thank you so much!, I feel so dumb because I've been trying to create an aggregation table but didn't realize I needed to create a query with the aggregated columns, duh!!
Funny Story...I'm listening to you now on Humana's ITLW, but I need to finish this report and needed this video. Thank you soooo much. Between this video and what you are talking about now This is exactly what I needed!!
@GuyInACube
4 жыл бұрын
Awesome! Thanks for watching!
Hey many thanks for your amazing and useful videos. I have a question, I have built a dashboard and I can filter anywhere I want to for instance by month or chart or geo point, but the only thing doesn't work is by group age. In my raw data I have certain group of ages from 0-5, 6-17,18-59, and 60+ for male and female. But whenever I click on any group age which only filters it self not other graphics in the dashboard. I don't know why, is it because I have numbers in the group age and it must be in text . Many thanks, looking forward to hearing from you. I am sure you will find a solution.
Thanks for the great video! At 1:09 the cardinality between DimDate and aggFactOnlineSales was \*\-1 ; and at 1:44 the same cardinality was reversed to 1\-\* . Was it initially misconfigured or is the switch a prerequisite for changing the storage to Import mode?
It is great, really helpful!
@GuyInACube
4 жыл бұрын
Glad it helped. Thanks for watching! 👊
You're the man!, thank you so much for this bananas video
@GuyInACube
4 жыл бұрын
Most welcome! Thanks for watching 👊
Great video Patrick, awesme feature!!
@GuyInACube
4 жыл бұрын
Thank you! 👊
hey Pat , need more details about using GROUP BY here vs setting Relationships! what is the link to the next video that you are talking about for this?
Hey Great Videos i have one question though can i apply same strategy in SSAS tabular model will it work there I have 500 millions rows in my fact table. It takes lot of memory in AAS . By applying aggregation as shown by creating aggreagted table for different grans like product Id, date id, country id can i achieve optimization.
Could you please tell me that do agg tables work in power bi report server?
What??? This is amazing... thank you very much!
@GuyInACube
4 жыл бұрын
Thanks. 🤛
This is more than bananas!
Will the DQ work in a composite model if the dim tables and fact tables are coming from different data sources?
Millions thanks
can you use this in direct query only or does it have to be dual mode?
Hi is there any way I can connect power BI with DynamoDB?
This is Awesome!! coooooooool.
@GuyInACube
4 жыл бұрын
Excellent! Glad you enjoyed it Mohamed! 👊
Patrick! It seems that you can't switch a Direct Query to AS (In my case a Direct Query to a Power Bi Dataset) to Dual Storage mode. Is there a way around this? Can I still get any benefit from aggregation tables?
Wow this is really great
@GuyInACube
4 жыл бұрын
Very appreciated! Thanks! 👊
This is a powerful feature. But I had a question on the relationship. Since the aggregation table is related based on DimDate, how does PowerBI know how to filter data for a specific Product in the DimProduct table? A date could be linked to any number of Products. Not sure if I missed anything
This is a great option! I wonder, if this also improves the slicer performance when dealing with large amounts of data?
@GuyInACube
4 жыл бұрын
Your slicer is typically coming off of a dimension table which should be fairly small - not large amounts of data. I do know of some customers that do have large dimensions, but that is not common. In an Aggs scenario, the dimensions should be duel mode and would be imported for use against the Aggs. So, ideally the slicers should be fast.
I am looking to export detailed data of visualization created in my report, whenever I export data it gives me summarized data the underlined data is greyed out hence unable to export detailed data. Please help.
I need to aggregate the sum of a column grouped by some values in another column. Is there any way to do that? I am using Excel as input. What i want is select sum(amount) from tablename group by date
How did you import Date Table in direct query mode...Did you already have a date table in SQL database?
@guslopez4505
2 жыл бұрын
Hello I had the same question and saw that he set it all up in this previous video. Basically it's a table made PowerQuery to do the legwork. kzread.info/dash/bejne/d5x7qJVwYteWo7g.html
OK Patrick--question. At the beginning of the video you said to switch the storage mode for the non-agg tables to Direct Query, but then in the very next step it switches some of the tables to Dual. I get that the table storage mode can't be Import, because then they can't be switched to Dual, but why not switch them to Dual from the get go instead of Direct Query? What am I missing? :)
@GuyInACube
4 жыл бұрын
You are not missing something. You could switch them to Dual before configuring the Aggs table. If you hadn't, it will then give you the prompt. Good call out 👊
@alt-enter237
4 жыл бұрын
@@GuyInACube Thanks! I am always second guessing myself! :) Stay healthy guys! We need you for our sanity!
That's really great - I was just wondering if you could use a DAX calculated table as your aggregation table? I tried to do that but all the details tables are greyed out...
@GuyInACube
4 жыл бұрын
It should work. It just matters that you have an imported table. Then you can do manage aggregations on that table. The details table needs to be in DirectQuery mode currently.
@jonnylindsay3645
3 жыл бұрын
@@GuyInACube thanks for this clarification - might be a post to pin as I've taken quite a while playing around with this before realising that because my detailed table is also in import mode this won't work... thanks!
Ok, speed is enhanced, but user must be aware that inconsistency of results may result: aggregation-table based results are updated ONLY once in each refresh cycle, non aggregationtable based results are updated if database has changed. So if I have only two categories, A & B, Total Quantity for "ALL categories) might not be (Quantity for Cat-A) + (Quantity for Cat-B)
great tutorial but can you please minimize the movements of hands...im getting dizzy with all the movements.
@GuyInACube
4 жыл бұрын
Appreciate the feedback 👊
That's 🍌🍌🍌
@GuyInACube
4 жыл бұрын
Thanks. Bananas.