Overview which field is used on a visual? In a calculation of a column or measure?

[Anonymous]

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?

[Anonymous]

*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.

[v-sihou-msft]

@Anonymous

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,

[KarlinOz]

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. 

[Anonymous]

I'v just posted this idea! Please vote!

[KarlinOz]

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...

[Dog]

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! 🙂

[Anonymous]

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

• with dax-studio i can get a list of the original fields in a table
• with dax-studio i can get a list of calculated fields in a table with the name and the formula
• with dax-studio i can get a list of measures with the name and the formula
• in excel a create a table 
  • in  C1 .. AZ1 i put the name of al the original fields
  • A2 i put the name of the calculate field,  B2 the formula of that calculated field
  • C2 contains a lookupformule to search if the name of the original field in C1 is contained in the formula-description B2
  • same C3 contains a lookupformule to check if the fieldname in C1 is contained in B3.
  • same for D2 which contains a lookupformule to check if the fieldname in D1 is contained in B2
  • and so on ...
  • problems: seems to work for easy formulas --> but not a solution for chained formules as field Y depends on field 1 and 2, field X depends on Y and 3 --> X depends on 1, 2, 3
• same technique in a new tab with all the fields (original and calculated) horizontally and all the measures vertically
• problem is a lot of copy of past from dax-studio to excel
• problem is that it is not dynamically --> you have to do it all again after some changes in the pbix
• problem is that there is not a connection with the visuals

Kind regards

Eddy

[Dog]

Hi @Anonymous

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

[Anonymous]

@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.

[BarAvi]

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

[Chris99]

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

[Greg_Deckler]

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...