Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Export of underlying data is exporting cartesian unrelated rows.

This appears to be the converse of the problem reported here:

 

https://community.powerbi.com/t5/Issues/Export-underlying-data-returning-only-totals-row/idi-p/70607...

 

I'm using the following setting for this report:

image.png

A matrix is filtered by a slicer as well as several report-level and visual filters.  Using DAX Studio I captured the report query used to render the matrix, and returns correct data.  And If I drill down on rows, the lowest level of detail is also correct.  

 

However, when I export underlying data to Excel, it is returning thousands of rows that are not constrained by the relationships in the model.  I captured the export query, and it's adding a table variable called __DS0PrimaryPreShowAllShowAllCompat which it UNIONs with a table variable called __DS0PrimaryPreShowAllReordered.  The variable __DS0PrimaryPreShowAllShowAllCompat consists a CALCULATETABLE function with several nested CALCULATE, FILTER, VALUELS, SUMMARIZE, ISBLANK, GENERATE, VALUES, and KEEPFILTERS functions.  However, it's causing rows from one of the related tables to appear that aren't in the filter scope of the matrix.  

 

If I comment out the __DS0PrimaryPreShowAllShowAllCompat variable and remove it from the UNION function, the results are what is expected, matching the summarized data, only within the scope of the filter values.  In this case it's causing problems for our customers in that it's returning dimension rows unrelated to the relevant facts.

 

Can someone please advise whether I can disable to generation of the offending variable?

 

Status: New
Comments
v-qiuyu-msft
Community Support

Hi @davisbizhq

 

Please check my latest post in this thread: https://community.powerbi.com/t5/Issues/Export-underlying-data-returning-only-totals-row/idc-p/71234.... Check if the report hit the limitation. 

 

Best Regards,
Qiuyun Yu 

davisbizhq
Frequent Visitor

@v-qiuyu-msft  Qiuyun Yu, hi.  Yes, I referenced your posting at the beginning of my posting.  The issue I'm seeing appears to be similar to yours, but we're getting too much data on export.

 

I should clarify that I used the wrong description.  What's happening to us is we're using Export Summarized Data.  Microsoft is adding a variable to the rendered query, and a UNION function to add that variable, which displays detail that isn't desirable.

v-qiuyu-msft
Community Support

Hi @davisbizhq

 

It possible for you to share the pbix file? If it is, please remove sensitive data then upload it to your OneDrive, paste the share link here. 

 

When using the Export Summarized data, please note: "select this option if you want to export data for what you see in that visual. This type of export shows you only the data (columns and measures) that you chose to create the visual. If the visual has an aggregate, you'll export aggregated data. For example, if you have a bar chart showing 4 bars, you will get 4 rows of data. Summarized data is available as .xlsx and .csv". See: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data#from-a-visualiz...

 

Best Regards,
Qiuyun Yu 

 

davisbizhq
Frequent Visitor

Qiuyun, regarding "This type of export shows you only the data (columns and measures) that you chose to create the visual. If the visual has an aggregate, you'll export aggregated data. For example, if you have a bar chart showing 4 bars, you will get 4 rows of data. Summarized data is available as .xlsx and .csv". 

 

I have read all of this.  The reported issue is that the visual is not behaving in the manner described above.  There is a bug.  It appears to be making incorrect use of KEEPFILTERS on rendering the query.

 

I have a support ticket open for this. 

davisbizhq
Frequent Visitor

I have figured out why this is happening. I had read Alberto Ferrari's article (https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/?nu=13545) on the use of measures to filter slicers and realized the concept is transferrable to this situation.

 

The query generated by Power BI is using GENERATE to create cartesian results based on related dimension tables that aren't designated to cross-filter in the data model. So we're getting unrelated dimension members that point to BLANK() measure values. The visual hides these by default (unless you specify "Show items with no data"), but the export does not.

 

I'm able to prevent this problem by filtering the visual so that it doesn't display data where the measure value is BLANK. This causes the unrelated dimension members to be excluded from the export.

 

I would still submit that this is a bug. The export should work like the visualization.