Latest Enterprise DNA Initiatives

Analyse in Excel - Pivottabel Drillthrough

Software & Tools
#1

Hi guys,

So Management wants to keep using Pivots (and the underlying data in tables) in Excel.
I have made some datasets in PBI and they were published to the workspaces in the PBI service. Then I did the Analyze in Excel and I made some pivottable reports that are connected to these datasets. So far so good.

Now if I drillthrough (doubleclick) on let’s say the grand total of the pivottable, Excel opens a new sheet that contains the “raw data” of that total (more or less, because of the measure used in the pivottable was distinctcount, then it allso includes the doubles in the “raw data”).

Problem is that the “Raw data” doesn’t seem to include the columns that I used to build the relationships in the original dataset (ex. postalcode in the Factsales table wont show in the drillthrough data of total sales, because postalcode has a relationship with DIMTABLE “Adress”. I use some dimensions of this Dimtable Adress on the rows of the pivottable).

Is this a know issue in Excel/Analyze in Excel ?

Can I solve this with an option somewhere ?

Kind regards,
Martin

#2

Have you tried :
Create a PivotTable from Power BI datasets - Excel (microsoft.com)

#3

Hi @Martin2140, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

#4

Hi @Martin2140, we’ve noticed that no response has been received from you since the 6th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

#5

I Solved it myself by just adding a Calculated Column which contains the colom where the relationship is made…so essentially making a copy…
then on drillthrough in Analyze in Excel you see that column…