Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
eddydm
Advocate II
Advocate II

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
Anonymous
Not applicable

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

@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
Super User

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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.