REDUCE Power BI dataset size by 60% with ONE CHECKBOX???

[@gulhermepereira249]

Great advice, Adam! Can we get more content on further optimization? That would be awesome

[@GuyInACube]

Of course! We have several already - I need to put a playlist together for those videos to make them easier to find. We do want to do more in that space when we encounter them though.

[@ernestnonso9624]

Hey Adam do you have anything on Tabular model optimization? I have a Slow dashboard on live connection. What is my best option to for optimizations??

[@MrRcfantini]

@@GuyInACube that should be great man! Sometimes minor issues have great impacts in our models and these videos help people not so specialized like me! Great job!

[@thiagofraletti4220]

How do you guys don't have like 10x more subscribers? Your contents are so professional and and amazing. Thanks again for one more valuable kownledge earned.

[@GuyInACube]

haha appreciate that. Let me know if you figure it out ;) 👊

[@Sandhyaansoman]

@@GuyInACube I would say you guys are hard to reach for any queries..

[@apamwamba]

@@GuyInACube Am in Zambia, Africa and i am a subscriber..Hope you visit Zambia one day.. Very useful content here...

[@SolutionsAbroad]

Great tip on this Adam, auto datetime really helps if you have a small dataset to work with and to get started quickly, however I don't think its widely known to create your own date table for larger deployments. I hope this auto datetime feature is disabled in the future so new developers are forced to learn about creating their own date tables

[@jacheto]

Woah! Now I'm always going to check the min and max dates, thank you so much

[@jamesgebler5738]

This video was super helpful. Using DAX Studio I was able to find out which unnecessary columns were taking up a lot of data and get rid of them which reduced my file size more than half from 1.45 G to 636 MB (which is about 0.6 GB). Then I watched the "Why is My Power BI Refresh So Slow?" video to speed up my refresh, which also was super helpful. Thank you!

[@nathanymiguel]

This is one of the best tips that I heard this year! You guys rock!

[@GuyInACube]

Love that! Thank you so much 👊

[@shiroe2102]

What is your tip/advice if my largest column is my identifier which is a column measurement (concatonate of two cells in the same row) and that identifier is for my Dax measurement to connect to other table. how can I reduce the dataset if i can't remove it.

[@jayantkodwani7868]

This is Ultimate, I just reduced 250 Mb dataset to 25 Mb and PBI file size reduced from 30 mb to 4 mb. . Thank you Guys for the simple yet highly efficient tip. I recently started hating PBI as to why the same thing is way more faster in MS Excel but now I think the performance should be much more decent! Falling in Love with PBI again! Woohooo

[@poujor]

Yes this is very effective. I never understood why this auto time intelligence is enabled by default. Also you are most likely required to create your own table with consecutive dates to ensure time-intellegence calculations can be done properly. And of course proper relationships is to be created.

[@GuyInACube]

There are some scenarios where it can be helpful. But this particular issue could hit even really small models. From my experience, it isn't that comment to see a year of 9999, but even smaller values can cause an issue.

[@poujor]

Guy in a Cube The one with the year being 9999 that comes to my mind is Temporal tables, but here the 2 columns ‘validTo’ and ‘ValidFrom’ should be concidered carefully, if included in the data model

[@joej.1995]

cool tip. any videos that explain the benefit of keeping it on? Kinda confused on when to turn on/off as i'm creating my reports now. thanks

[@ms0024]

This is a great video! I am importing a csv file(17gb) with 127 million rows and 13 columns.There are 2 date columns.Thing is I already clicked import and PowerBi has been sitting at the "LOAD" dialog box for about 1 hour.While that was going on I stumbled upon your video.So just wanted to know if i disable auto date time, will it decrease the import time since size has reduced? I wish I had you and Patrick as my college Professors for data analytics. Thanks a lot for your work.Your interactive,highly fun and exciting videos is what keeps amateur data analysts like me from scratching my head to "Let s' Head to GuyInACube"! Again, Thanks a lot!

[@jayong5877]

Awesome! I learnt something today. Excellent in-depth explanation.

[@mnesevati]

Hi dear, is there a way to allow the power BI user to edit or update the figures which are existed in a table or a matrix, my idea is this table contains their projections for a specific product sales over the FY and I manually compiling the projections from the concerned manager and upload them to Excel sheet which is linked to power I, but. I need them to be able to update any change in their products plan sales directly to that table in the excel sheet, I hope you got my point

[@RenatoHaddadMVP]

Great tip, thanks. Why is not available to disable LocalDataTable per table?

[@ahmedmj8729]

Thanks for Help Adam , but if we uncheck this option , can it have bad effects on our model , measure and calculated columns (because i have many columns used dax functions based on Date) ?

[@ketanpatil3489]

very nice Information... sometimes we do tend overlook these power bi file settings and options. Adam & Patrick - do you have any previous video on options and settings?

[@mattsnow2376]

I have made a mistake like this before and ran the VPAX analysis and quickly discovered this was the culprit. Thanks for the reminder!

[@fernalication]

That's a nice one. Is it supposed to be used at the very last step of development? I mean, if not, you loose the date column format and the hierarchies too, am i right?

[@GuyInACube]

If you have a date table you can build the hierarchy in the that table.

[@juanlauroaguirre5646]

At work we have a huge shared dataset with this issue (auto date-time enabled) the problem is if we deactivate it many reports (of about 70 total) using such would break, any suggesions?? thanks!

[@dandehaven484]

WHAT! I've had a creeping suspicion I should uncheck that for a while now. Talk about needless redundant overhead. Great Tip!!!

[@debasreetripathy5962]

Eye-catching caption and great explanation of the solution. Thanks adam for this awesome content. I have a question that can we use month slicer with relative date filtering . If we are using month slicer and want to see data for current month even if anything is not selected in the month slicer,how to do it having 2 or 3 years of data.

[@sikandrasingh2481]

Go to power query editor and find the date column, apply filter at date column as (is in previous) 3 years or year is in year to date. This will bring the data only for the current year to date and last 3 years

[@debasreetripathy5962]

@@sikandrasingh2481 thanks for replying but it's not the answers to my question. I am not asking here that how to bring data in report for 3 years,your ans is preferable for that.

[@agasti2007]

Extremely valuable tip! It reduced my model size by 40%. Cheers!

[@amansuman2005]

Hi thanks for sharing this it’s just awesome; one quick question I have a model in excel and using it in power Pivot. Facing same data size issue ... how to check the same stuff on excel

[@wilsonusman]

You guys are great as usual, thanks for the new tip, I just downloaded dax studio and the tabular editor. I feel like a total newbie.

[@IstvanSchiszler]

My problem with custom date table and built date hierarchy is that in line charts I loose the abilitiy to select continous X axis. Do you have any workaround for this problem?

[@DynamicSphinx]

Does this also speed up Power Query transformations? Thank you

[@alfredsfutterkiste7534]

Hugely satisfying video even though I knew what it was about straight away when I saw the checkbox in the title. Bringing it down like that for a customer must feel great.

[@Tamashiwa]

Excellent clip, this will save me time when needing to explain in the future! 😃 Wildest one I've encountered so far : 120+ date fields coming from a system that used '1900-01-01' as a default date for unknowns, exceptions, .., and '2100-01-01' for dates that were yet to be determined. I had some fun with that 😉

[@GuyInACube]

Nice! Did you catch the other extreme value in my table? That's what got me to the 3+ million cardinality and it surprised me that i could have that as a date value.

[@matthewdufty606]

Excellent video. Thank you

[@Jack2of3]

I have a curiosity question. Is it better to clone a table within Power BI or re-import the metadata? From an ease of development cloning is my preferences however if I have 16 hierarchies of a internal date table for time measures which is more efficient for processing? The metadata import or the cloning or no difference. i.e. does the metadata table import create a new DB connection pool and does the cloned create a new DB connection pool or does one or the other resource share the connection ? Same question for PBI. Yes all 16 hierarchies are needed for the Purchase to Pay process. Thanks.

[@seanvsn]

Wow this insight is great man! I went from 700 Mb for 10.000 rows, to 800 Kb for the same amount of records. Absolutelly INSANE. Thank you for this video, I was getting mad at my report because of this madness. Now I'm chill again :D.

[@LetsTalkPowerBI]

Great video! I change the link in my blog post already ;) the old video I found was also good but not soooo detailed :D

[@GuyInACube]

Nice! 👊

[@nagasatyadevarakonda4269]

Great information.Quick question: Is this also applicable if you are modeling in VS for SSAS direct query models? I am curious to know.

[@ja3mi2]

Great video! Would be nice if monthly updates to PBI would respect previous global / local settings... (Looks like it does for auto-DateTime, but it doesn't for relationships...) Also - I have been looking high and low for PBI bling. Other than your Guy in a Cube merchandise links above, where can I get Power BI (T-Shirts, Mugs, & Stickers)? Thanks again for all you do! Learning tons every video... John

[@GuyInACube]

Yeah I can see how that would be frustrating John. For the merch/bling, Microsoft doesn't actually have any real merchandise for sale :( I keep telling them that they need to :) I get that it can be complicated though. Really you have to go to conferences to get it - or know someone.

[@ja3mi2]

Guy in a Cube I know you and Patrick! ;-)

[@justinnuval6418]

you saved me a lot of trouble with this video. subscribed

[@caantu95]

Adam, I think that this video has the answer to my issue. You know, I'm working in a model where I need to pull 48 sheets from 4 different excel files. And when I'm updating and eliminating columns ( all 48 sheets have around 200 trash columns ) , in the second or third document my laptop goes crazy with no memory to continue working (32gb of ram). Do you think that this could help me to downsize my data sources in powerbi? Thank you!

[@jeovarghese9748]

Amazing Adam.. My refresh with 1.5 GB was failing complaining out of memory. After implementing your tip, it work like a charm. Thank you ☺️

[@GuyInACube]

WOOT! Awesome to hear. 👊 Was in a good discussion yesterday with some folks about the fact that people just don't know about this.

[@thomasschaub9474]

Hey Adam - nice video! Can you give me advice for handling a SharePoint dataset in DAX / PBI where my data has numerous date/time fields per record (eg start / stop date/times for phases of a project)? Is it best to create a calendar table then have an active relationship plus numerous inactive relationships with the table or is there a better option? TY!!

[@KehfunIkemba]

super helpful! thank you

[@prosector4]

Managed to reduce my model size by more than 55%.. thank you

[@danny5320]

Yeah, reduced my file size by 36% with unselecting that one checkbox. Victory!

[@krishnakishorepeddisetti4387]

Very useful information... thank you 👍

[@vladimirkuksenko923]

can someone explain wtf is auto date/time and what its puprose. thank you in advance

[@julieyu646]

Super amazing! Thanks!

[@helloreddy101]

As per video because of year 9999 the table size is 1.64 gb. After fix the issue the use still able to see 9999 date?

[@BajkeTheSerb]

Awesome. Thx for the tip!

[@notoriousft]

Great video as always.

[@andyparkerson]

I like how Adam's first thought when the customer says they want a P4 is to talk them down, instead of replying, "Maybe let's go to a P5 just to stay ahead of the curve."

[@GuyInACube]

We are always about optimizing instead of spending money. To be fair, we aren't in sales. :) That being said, by the time they get to us, they are in a crunch and will sometimes scale it up as the band aide, but then never go back and fix the problem. it's the quicker thing for the short term.

[@avinashnl9588]

Hi patrik, How are we doing if we have SSAS Tabular model connecting to power bi.

[@ivanmezev]

Open Dax Studio from PowerBI and then click View Metrics

[@avinashnl9588]

@@ivanmezev request you to please provide some doc if you have on how to achive it.

[@aashaymehta5436]

Great video Adam. Unrelated, but a question I had was regarding sending custom data alerts via email. I saw the video on using Microsoft Flow, which helped a lot, but what I would like to do is pull some additional information from the dashboard and mention it in the email so the user does not have to open the dashboard. How would you do this? I appreciate any help :)

[@zakincc]

You guys are awesome 😎

[@GuyInACube]

Thank you Zakaria! And, thanks for watching 👊

[@vishalsukhija4111]

Great video Adam! Any way we can have the local date hierarchy table created for only one object, say a date dimension? The only way I see is disabling time intelligence and creating columns for month, year and quarter

[@shraddhasingh6161]

Superb👌!!!

[@GuyInACube]

Appreciate that Shraddha! Thanks for watching 👊

[@_radON]

You might just have got me one recommendation with this 😀

[@JackCastro]

You guys are awesome!! Thank you 😊

[@FredLorrain]

Turning off this option is well known BUT what about managing the consequences. What is the impact in the visual and date hierarchy? Some date fields never used in aggregation could be replaced by formatted text field. Some others will need to be related to a single Date table. Very often a single Date table is not enough. Turning off auto date table need to be done very carefully.

[@GuyInACube]

The idea is you should have a central date table to handle those items. Why wouldn't a single date table be enough?

[@AdiCristea]

I may miss something here, but isn't 69.3 Mb roughly 4% of 1.7 Gb, therefore the size was reduced by 96% which is even a bigger achievement?!

[@chaitanyadhole4864]

It's insightful!!

[@GuyInACube]

Glad you thought so. This was a fun one for us.

[@maily3033]

Thank you thank you thank you!!! This saved me at work!!

[@brypie04]

Brilliant tip!

[@DiwakarJha]

Thanks for sharing this important tip

[@mnowako]

Excellent! Thanks.

[@GuyInACube]

Thanks for watching Mariusz! 👊

[@Royalsomu]

Wonderful update Adam sir

[@brianspiller9075]

Awesome, especially if a source is prone to garbage/invalid dates. GIGO ! I am seeing recorded dates from thev 1st century to 70th century due to data entry errors. Maybe when i need a laugh I'll do a Load on that to see the auto-date table sized. Otherwise its just too depressing to know the validation didn't/doesn't occur in some cases.

[@VikingGuard]

Never used auto date table. Always use custom calander table. This is nothing new, has been out there for quite some time. :)

[@jaydeepbiswas1]

This is great. ❤️

[@datawhipper]

I am kinda confused when you talked about upgrading to P4. What does that mean? 🤔

[@akulakalyan1242]

It's a premium capacity level

[@raviv5109]

Awesome!

[@ManafKAMIL]

Like, like and more likes. Great insider tip... 💪💪

[@dbankerph]

Wow...mind blowing fix...

[@chuyvarela3113]

Holy cow!!!!! This is ____________ (fill in the blank)

[@GuyInACube]

Right?!? Insane Amazing 👊

[@goldwhispers]

great tip, this just saved 3 power bi models from huge rework

[@ankitadharmik6910]

OMG... This is perfect... Thnk u so much 🤩

[@GuyInACube]

Great to hear! 👊

[@marciof]

Amazing!!

[@alanbarasch7847]

Noticed also that refresh is much faster after applying change

[@Kay-qg5td]

This is AWESOME!!!

[@j.k.ravshanovich]

5:58 go straight to the point

[@Obizzy8]

Awesomeness!

[@volkanmusabostanci8046]

Men, ı love you! I really love you guys

[@ColonelVirus]

Wow I feel extremely lucky... I watched a few videos, pretty sure it was one of your, about calendars and making your own. First thing I did, so I've managed to not run into this issue... because I have dates ranging from 1800-9999 (one program default dates to 01-01-1800 and another goes out to 9999 like in the video). Gonna turn this off still just incase ha. Thanks!

[@Phoenixspin]

Trying to calculate the date the Universe will end. I need those dates.

[@johnadair4979]

That's incredible.

[@nikolagrkovic8769]

With this I just reduced my database from 2 gigs to 116mbs. why you don't put Guy in a Cube videos in school?

[@truthandunity623]

How is 1.7gb to 60MB a 60% réduction?

[@robemieorpilla8162]

I have already turned off the time intelligence on a 1.2gb dataset. Well, maybe my dataset is really huge. :(

[@rakkhnaka]

6:01

[@kaly5834]

The real question is, how much do you charge the customer for a 10 min fix? That requires a very good understanding of data modeling.

[@GuyInACube]

Indeed. Didn't charge them anything as that was part of our day job. We don't do actual consulting.

[@ManafKAMIL]

Even charging the customer for 10 minutes job, it's still worth it. As you said, it requires a very good understanding of data modelling, which doesn't happen in 10 minutes!

[@lukeminikoski]

Ok, very old, but still gold!

[@martinhester49]

Old video, but just reduced my project size by a factor of 6. Woo hoo.

[@sue_bayes]

Crazy, can't understand why this is a default option.

[@GuyInACube]

There is a lot of debate about this, but it addresses self-service needs out of the box.

[@nikakalichava8012]

wow.

[@cibelevales]

AMEI!

[@MJ-YT-USR]

Those pesky dates - probably the most painful data item you can deal with and overcome, particularly when you Americans flip the day and months around too - grrrrrr!

[@Juttutin]

Sigh. First principles. One atomic field, one atomic use. The problem isn't really anything to do with BI options, it's repurposing/forcing a date (year) field to have dual meanings. 9999 is not a meaningful year, their data architect is the root cause here.

[@Juttutin]

Did your customer fix the misuse of the year field, or were they happy with just reducing the impact in the BI?

[@user-ho1hw7ps4v]

these ads about ai to plan my tasks.. IM at a point in life where if I procrastinate only 2 more years, Ill get a cheaper AI that actually does my tasks hell might get two and in a month ill be back on track

[@gowrisankarpokuri]

Omg, 1.7 Gb to 69 mb....wow

[@ynwtint]

Banananana..

[@patcalasukal]

Wtff hahaha nice

[@allentseng2869]

I recalled seeing these local date time tables in my model but not knowing what they were, then watched this video, aaaaand I'm done here