It’s downing a bit slowly on me how awesome the direct retrieval of DMVs in PowerBI and PowerQuery actually is. Just see how easy we can build a cool view of our memory consumption in PowerBI:
Left: Tables – Right: Columns
RAM/Memory Consumption of whole data model
Filter a table:
Filter a table to see its columns only
Just copy this code and paste it into the advanced editor: MCodeDMVMemory.txt
DAXers dream: Combine your DMVs on multiple columns 🙂
Credits go to Scott, Kasper, Mimoune and Igor
Things to watch for:
- The query retrieves the name of the current user by referencing C:\Users in the second step (“CurrentUser”). You might have to adjust that folder to your settings.
- Make sure that you only have one pbix-file open, as only one instance ID will be fetched and you cannot influence which one that will be. So if you just keep one file open, that will be it.
- You might get a warning on privacy levels. Either set them to public, ignore them or adjust the code like Ken Puls has described.
If you experience RAM-problems already, make sure you didn’t fell into this trap.
Enjoy & stay queryious 😉
CooI advice, thanks! If there will be the same cool tool for M…
PS I can see this beautiful Hawaiian sunset there 🙂 For me it ended up before DAX even comes to scene (come see that thread: https://twitter.com/Hohlick/status/871642405627465728 )
You bet – I would even pay money for it 🙂
Will publish some new functions soon that helped me with that dataset (you can have a sneak preview here: https://www.youtube.com/watch?v=5q1WhWjbz5Y)
Cheers – Imke
Oh, I know this problem with arrivals data. They didnt sum up because one tourist can visit several islands, thats why you should use separate value of “Statewide” to show up correct data on state level, and then also Hilo and Kona is the two sides of one Hawaii Island, so they 1) didn’t sums up to the total of Hawaii Island and 2) are just details. I buried inside all these agregations and granularities and almost get lost there 🙂
Yes, pretty asymmetrical aggregations there. Will be interested to see how others solved it. Hope they will be published 🙂
Yeah. Does not work. Invalid identifier: Folder.Contents(“C:\Users”),
Yes, sorry – forgot to mention: The query retrieves the name of the current user by referencing C:\Users in the second step (“CurrentUser”). You might have to adjust that path to your settings.
There are some issues with privacy level!
Yes, it only works if you set them to public or ignore privacy levels. Otherwise you have to rework them according to this technique here: https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
Great blog post and really helpful. One question that I have is that do you have to only have one Power BI Desktop file open when running the above?
I tried it with one open and it worked perfectly, but if I have 2 Power BI Desktop files open it comes back with incorrect data?
Thank you! Yes, it should be only one pbix open.
I believe it will fetch the ID of the file that has been opened last, but am not sure and haven’t analysed it systematically. So the data will probably not be incorrect – just belonging to the wrong model 🙂
If you have more than one pbix open, you can add a step in the query that sorts the results in the folder before you select the port ID, thereby choosing the ID of the second most recently opened file, allowing you have your actual report open, then open the memory report and have it analyze the pbix you are building.
Thanks for that David!
Pingback: BI-RoundUp – Power BI (Service Update April & May – Personal Gateway Update – How to Purchase Power BI Premium – Managing Power BI Premium – Memory Consumption in Power BI File – Power BI – Exposing M Code) – Gilbert Quevauvill
Pingback: BI-RoundUp – Power BI (Service Update April & May – Personal Gateway Update – How to Purchase Power BI Premium – Managing Power BI Premium – Memory Consumption in Power BI File – Power BI – Exposing M Code) – Power BI Business