Export Underlying Data & Measures Tables - how to link to main data tables

[Anonymous]

Hi,

I've been having issues exporting the underlying data from my report in the Power BI Service. When a user clicks this they're only getting an aggregation of the measures within each visual

I've spoken with Support and they say it's because I've moved all my calculated measures to a separate Measures table that has no relationship with my main data tables. 

I created separate measures tables in order to keep my data model clean - I have so many measures and this make it much easier to find and sort them all. I also give my reports to other users and it's an easier way for them to find their way around. When I get up my tables I created a blank table, moved a measure to it then deleted the redundant column. I've been doing this for the past year and never had this issue until this feature was release.

I've been told I have 2 options - move all my measures to their original tables, or find a way to create a relationship between the measure table and the main data table. Can anyone suggest how I can do this? My main data tables are linked by a series of reference tables.

Thanks,

MarkJames

[Anonymous]

Hi @Anonymous ,

I hope you are looking to export the data from the Power BI service from the visuals of the report. Could you check

the options under the Current file where we can set the type of data allowed to export. You can ceck the selection based on your requirement there. Find the attached screen shot for your reference. 

It will allow the power bi service visuals to export the underlying data also.

[Anonymous]

Hi @Anonymous ,

We've checked this and the second option is selected for all my reports. I logged a ticket with Power BI Support who took me through all these options and said it's because my measure tables don't have a relationship with their data tables.

I'm looking for suggestions to help me create a relationship between these that will let me export the underlying data. I need a relationship between my CALENDAR table, the MEASURES table and my DATA table.

Any suggestions as to how I can do this?

[harip1]

Hi @Anonymous ,

Even am looking for something like this. i don't see any solution added to this post.

if you don't mind if you had the solution can you post it here.

Thank you.

[v-piga-msft]

Hi @Anonymous ,

I have a little confused about your scenario.

It seems that you have problems when using the Export data option for the visual in Power BI Service.

If so, we always could get the underlying data like below when export as xlsx.

Is this your issue or output?

Best Regards,

Cherry

[Anonymous]

Hi @v-piga-msft ,

Isn't the example you've given only the summarised data and not the underlying data?

When I spoke to Power BI Support they said that because my Measures sat in a different Home Table from the data that this was causing the issue. Below I've given a screenshot of the relationship in my model:

I'm connecting them all through on Calendar table. You'll see my Key Measures tables on the left - one for each data source.

Power BI Support said I need to connect these Key Measures table to the Calendar and relevant data table in order to get the underlying data export I'm after - where I get a row by row extract of the data.

I was told perhaps to try SUMMARISECOLUMNS to create a Table to use for my Key Measures but I'm not sure exactly what fields to use and how to link them to my data. I initially tried creating an INDEX column in my data, then using that along with the DATE from my data but it didn't work.

Any thoughts on what I should do?