Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
At this moment I have different sheets with more than one visual on each sheet.
I'v multiple datasets (tables) with too many fields.
How can I get a documentation which mentions which field is used on a visual in this desktop file? or in a calculation of a column or measure?
Purpose is to minimize the size of the pbix-file by eliminating tables i didn't use any more, or delete fields in a table if i didn't use it in a calculation of another field or in a measure or in a visual.
I know that by using DAX Studio I can get a list of column- and measure-definitions. Perhaps there is also a possibility to get a list of items used in a visual?
*years later*
This has turned out to be a great tool for inventorying which fields are used in any report: http://radacad.com/power-bi-helper
It does not examine which fields are used in measures or calculated columns, but DAX Studio can do that for you.
As @Greg_Deckler mentioned, in Power BI Desktop, when you click on a visual, the involved dataset will be highlighted into yellow. If you expand it, you will see data fields used (which is selected) in this visual. Currently, this is the only way to find those data fields in use.
Regards,
I wonder if anyone has submitted a feature request for this. I have a project with lots and lots of visuals. To do this manually I'll need to click on each visual, manually make a note of every field used, <<cliick on the next visual, manually make a note of fields used...>> iterate. That'll take ages. But I would like to eliminate all unneccessary fields.
I'v just posted this idea! Please vote!
And the link to the idea is here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/18472060-list-of-used-fields-in-v...
Not sure if you've got anywhere with this, considering it's a year old. I'm too going through this process now.
I've currently got to the stage of extracting the files that make up the PBIX file to reveal a layout file (JSON format)
within this it seems to list all of fields used on visuals and filters etc...
I'm then looking at the tabular editor tool which allows you to check for dependancies on measures
still a long way to go so I thought i'd check to see if anyone else had made progress! 🙂
I didn't get a solution at this moment.
Can you give me more information about how you get the list of visuals and the list of fields used in those visuals, filters, ...?
I'v tried the following steps in the search of unneeded fields, unused fields
Kind regards
Eddy
Hi @eddydm
It's still very early days but I will at some stage need to make a start on it.
the PBIX files are compressed files from what I understand, so I used WinRAR to open the PBIX file and extracted to a new folder.
from here under the Report folder (within the extracted files) there is a "Layout" file. This is a JSON file
inside the file there is a section array (whicih contains all pages) and for each section there is another array object for the visuals and against these are the entities (which includes the table and measure/column)
what I still need to do is find a decent JSON reader which will allow me to drill down to the lower array for analysis.
I was just hoping that I wouldn't need to reinvent the wheel 🙂
Dog
@Dog 's answer: Thanks, very useful. The online json reader http://jsonviewer.stack.hu/ works great to view this data. Just copy-paste the json string from the Layout into the Text tab on this website, then click the Viewer tab and there you go, well formatted now.
Hi @Dog
Your answer was very helpful !
I notice that in each section there are visualContainers and inside them there are : id, x, y, z, width, height, config, filters, query, dataTransforms and etc.
I tried to find the fields that I see in query and metadata but I couldn't find all of them
Can you tell the meaning of query and metadata in the visual layout ?
Thanks
I know this was originally started a while ago, but I think it is worth highlighting Reza Rad's Power BI Helper. It's still a work in progress, but it is very useful, and free.
Chris
Couple ways but probably much more manual than what you want. In Desktop, if you click on a visual in the Fields pane, it will highlight the table and columns that used in that visual. Also, if you click the ellipses and choose Export data, it will only export the columns used in the visual.
Now, that does not help you with determining what other fields are used within your measures and calculated columns, that is a manual inspection of those columns/measures...
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
83 | |
65 | |
49 |
User | Count |
---|---|
127 | |
108 | |
89 | |
70 | |
66 |