cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eddydm
Helper III
Helper III

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

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?

 

 

 

12 REPLIES 12
mgcharumilind
Advocate II
Advocate II

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

@eddydm

 

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.

 

Capture33.PNG

Capture44.PNG

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!

Dog
Responsive Resident
Responsive Resident

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

  • 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
Responsive Resident
Responsive Resident

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

Anonymous
Not applicable

@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
Frequent Visitor

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

Greg_Deckler
Super User IV
Super User IV

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

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors