Trace dependencies

[masplin]

Hi Is there any way to trace dependencies in powerpivot to see what other columns or measures are depend on a particular measure? I want to make a change to a formula, but need to check it is only being used the the places I think I'm using it.

Thanks
Mike

 

[powerpivotpro]

If you are only concerned with other measures referring to that measure, I have a trick for you. (I have a way to inspect all measure formulas as a single text file).

 

[masplin]

hi Rob. that sounds perfect if you are willing to share. Just want to see if I change a measures formula what other things are affected.

Mike

 

[powerpivotpro]

I'll probably do a full blog post on this soon, but for now:

1. Rename the XSLX to .ZIP
2. Go into the CustomXML folder
3. Find the XML file that contains the text “CREATE MEASURE” somewhere near the end – that is usually the largest xml file but not always
4. Copy every line that starts with CREATE MEASURE – those are your measures. At that point some Ctrl-F find will help you find any references to your measure in question

I really, really like that you have a model with another interdependent measures to need this. A sign of some truly awesome usage. (We have this problem all the time but we tend to be the exception).

 

[masplin]

I needed a whole A4 sheet of paper to write down the trail of just one of my measures!! It's a complex beast these days!!!

Thanks for the tip

 

[Mavericks334]

You can use this step as well to check the dependencies of your measure.

Data- Connections- MAnage Sets - New- Create Set Based on Columns/Rows items - Select you measure - Edit in MDX - will give you the details that your looking for

 

[masplin]

I got as far as new set and selected users[repeat users] which is the calculated column I created in table users that I want to trace. I pressed insert but then got "set creation has failed". Also I'm not familiar with MDX having come to poverpivot form being a basic excel user.