cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ankibano
Frequent Visitor

Issue with "Underlying Data Export ".


Hello,

 

We have a table visual with few measures represented as columns.

 

While exporting the data from a table visual using "Underlying Data" feature, Power bI was not flatenning the exported data. Instead it was only exporting the measure values. The below points have also been checked :

  • The underlying data source table do have UNIQUE KEY.
  • Show items with no data is not enabled.
  • The feature is not disabled by Administrator or by a report designer.

 

We are wondering what can be the cause that the "Underlying data" feature is not exporting the actual Underlying data.

 

Note : Export to "Underlying Data" feature is enabled in both POwer BI desktop file and service.

 

Any suggesstion or solution would be really appreciated.

 

Thank you

7 REPLIES 7
collinq
Super User II
Super User II

Hi @ankibano ,

 

I am not quite sure what you are striving for - when you export the data, you are exporting the data that is behind the visual.  So, the underlying data will provide the data that creates the visual and not necessarily every single data source in the dataset.  Also, if you have RLS applied this will affect the outcome.  And, if you have filters applied then that will affect the output as well.  Aggregrations are flattened and removed as well.  There is also a limit to the number of rows being exported and that might be a limit for you.




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




ankibano
Frequent Visitor

Hi @collinq ,

I am trying to extract data from a table. The table contains Customer name, Year Month, and some measures (below screenshot). When I try to export “Underlying data ” from the table visual, it only exports the overall measure value and not the underlying data which are connected to it.

EXPORTED FROM :

 

Exported_From.png

 

 

EXPORTED RESULT Exported_Result.png

 

 

DATA MODEL

DataModel.png

Hi @ankibano ,

 

Thanks for the diagram.  I can't recreate the problem.  In my tests, it is working fine and as expected.  Would you be willing to put the file in a file share somewhere and let me access it and see what I can do?  I test with and without filters, with and without the sum field (fyi - without the summation field [the 5469] being in the aggregration, you can't do underlying data).




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




ankibano
Frequent Visitor

Hi @collinq ,

 

You can download the sample file from the below link :

 

 

https://drive.google.com/drive/folders/1RbGMnr1xTICDDff7Jh6jJwmO14zsHTDN?usp=sharing 

 

Br,

Ankita

Hi @ankibano ,

 

I received your file and tested it and got the exact same result, initially.  So, the good news is that it was replicated easily for the result.  The bad news is that this is not what we wanted.  So, I dug in a bit more and discovered some documentation that seemed to indicate that this was what should be expected (not by me or by you but by design 🙂  ).

Specifically, I saw this comment:

"What you see when you select Underlying data can vary. "

If a visual contains Aggregates, you will see "the first aggregate and non-hidden data from the entire table for that aggregate" and then all of these for "measures"

Visual contains What you'll see in export
Measures* all measures in the visual and all measures from any data table containing a measure used in the visual
Measures* all non-hidden data from tables that contain that measure (as long as that relationship is *:1 or 1:1)
Measures* all data from all tables that are related to table(s) containing the measures via a chain of *:1 of 1:1)
Measures only all non-hidden columns from all related tables (to expand the measure)
Measures only summarized data for any duplicate rows for model measures

 

Sooooo, I changed it up a bit and got the summary number WITHOUT using your "Total Calls" measure.  And, it worked fine for a full underlying data export.  In this file, YOUR initial box is on the left, my version on the right.  I have placed the full export and the file here for you:

https://pmps1-my.sharepoint.com/:f:/g/personal/cquiring_epmstrategy_com/Eh0j_jBBsu9HhUCMNpx_tF0BkhLe...

 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




ankibano
Frequent Visitor

Hi @collinq ,

Thank you so much for putting your inputs. So do you mean in this case the below condition is applicable ?

 

Measures *  All measures in the visuals and all measures from any data table containing a measure used in visual.

 

Although I want to use measures as a calculation in  a table ( and do not want to aggregrate a columnin the table).

 

Thanks !

Hi @ankibano ,

 

Yes, your situation is as you referenced - you are getting the result set without much data because of the measure being the aggregration.  Perhaps make an idea to change this behaviour?




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!