Raw data access

[JS23]

Hi,

I have the following use case at hand

1) dataflow loading data into datamart

2) semantic model with relationships and measures within this same datamart 

3) a few PBI reports in the service, using this semantic model as data source. Works perfect. Everybody happy 😉

Now, our financial analyst wants the flexibility and ability to do more ad hoc work on the data, answering ad hoc questions, do deep dives into the data when he sees strange thing in the data or needs more detailed understanding of the numbers. Basically want to be able to build his own pivot tables. 

My solution: hand him an Excel file live onnected to the online semantic model. This way the numbers and measures are always identical to what is shown in the online PBI reports. So far so good (at least this feels like a good solution unless I'm missing something)

BUT...

now he wants to go further and actually be able to see the raw data from the data model. Basically the thing that happens if you would double click a cell in a normal Excel pivot table, generating a table of underlying rows from the raw data.

what would you suggest to achieve this, or somehow replicate this double click experience

thx

Jan

[Anonymous]

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, @JS23 

Could you please let us know if lbendlin's response resolved your issue? If it did, kindly accept it as the solution.

Of course, if you have your own solution, feel free to share it. This will help other community members with similar issues find a resolution more quickly.

Although there isn’t a direct method to replicate the double-click experience of Excel pivot tables, there are some alternative effects that are somewhat similar:

1.Firstly, regarding the need to create pivot tables and charts while maintaining a real-time connection to the data model, you can use the “Analyse in Excel” feature. This allows users to connect Excel directly to Power BI datasets. However, this method does not directly replicate the double-click functionality to view the underlying data.

For further details, please refer to:

Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn

2.Secondly, concerning the analysts’ need to view the raw data, they can export data from Power BI reports. In the Power BI service, they can click on the ellipsis (…) in the visualisation and select “Export data.” This will enable them to download the underlying data in CSV format, which they can then open in Excel.

You may want to note that there are significant differences between exporting data in Desktop and in the Service:

For more information, please refer to:

 Export data from a Power BI visualization - Power BI | Microsoft Learn

Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

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

[lbendlin · Accepted solution]

Show them how to connect to the semantic model with DAX Studio, and show them the Query Editor there.  Basically, convert them from MDX addict to DAX addict.