Get Unused Columns/Measure in PBI Dataset where local Reports are connected to

[schlammi]

Hello there,

I have several datasets. And there are a lot of Power BI reports and Excel files associated with the datasets. The reports and excel file are not in PBI service, there are just saved locally.

Is there any way to find out which columns and measures are not used in the dataset?

I only have access to the dataset. I tried using a Rest API but I cannot find anything.

I need to find log entries of requests from these Reports and Excel Files to the Dataset. 

I dont think Measure Killer can handle this

Any Ideas?

Greetings

Yannick

[mark_endicott · Accepted solution]

@schlammi - If you have premium or PPU and the XMLA Endpoint read/write functionality enabled, you can use DAX studio to view the model metrics, at the table level you have the Temperature column which is a scaled number based on the frequency a column is accessed, and you also have "last accessed" to see when that column was last used. 

More info here: https://daxstudio.org/docs/features/model-metrics 

If this works, please accept as a solution, it helps with visibilty for others. 

[Brunner_BI]

@schlammi 

I assume this has not been fixed yet according to your requirements.

So in fact Measure Killer can handle this in two ways:

-It has a function to find all the local Excel files that are querying your semantic model (paid feature)

-You try to get all the local files (.pbix or .pbip) and then plug it into the tool

There is no reliable way (at least that I know of) that tells you where those local files are, they do not show up in the logs properly.

[Anonymous · Accepted solution]

Hi @schlammi ,

Yes, there are a few options for identifying and deleting unused columns from shared datasets in Power BI.

1. Power BI Helper Tool: Power BI Helper is a free tool that allows you to analyze and document Power BI reports. It has a feature called "Column Usage," which can identify unused columns in a shared dataset. You can select the shared dataset and run the Column Usage feature to see which columns are not used in any reports. You can then remove those columns from the shared dataset. This tool is available for download from the Microsoft AppSource.

2. DAX Studio: DAX Studio is a free tool for analyzing and debugging DAX queries in Power BI. It has a "Column Usage" feature that allows you to identify which columns are not used in any measures or visuals. You can connect to the shared dataset using DAX Studio and run the Column Usage feature to see which columns are not used. You can then remove those columns from the shared dataset.

3. Bravo for Power BI: https://www.sqlbi.com/tools/bravo-for-power-bi/ 

You can also look at this two topics: Solved: Identify and delete unused columns from all report... - Microsoft Fabric Community and Solved: Identifying all unused columns and measures in Pow... - Microsoft Fabric Community

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mark_endicott · Accepted solution]

@schlammi - If you have premium or PPU and the XMLA Endpoint read/write functionality enabled, you can use DAX studio to view the model metrics, at the table level you have the Temperature column which is a scaled number based on the frequency a column is accessed, and you also have "last accessed" to see when that column was last used. 

More info here: https://daxstudio.org/docs/features/model-metrics 

If this works, please accept as a solution, it helps with visibilty for others.