Export Underlying Data from joined views- columns from second view are not exported

[elizachan]

I have a semantic model with two joined tables, with active many to many relationship, corss-filter direction: both. In a report, there is a simple tabular visualization that includes some columns (descriptive, dates and measures) from both views. I have enabled the export of underlying data in the report options, and users have build permissions. However, when users export the underlying data from that visualization, columns from the second view are not included, even though they are visible in the report. There are no "extra" security measures on any of the data. How can I ensure that the underlying data from both views is exported?

[Anonymous]

Hi   @elizachan 

I did some tests and it showed that it might be due to the many-to-many relationship.

Firstly I created a many-to-many relationship on Month column in two tables (DimDate table and Fact Table) and set the cross-filter direction as Both. This only gave me data from one table when exporting the underlying data. Then I modified the cross-filter direction to Single (DimDate table filters Fact Table) and received exactly the same result when exporting underlying data. 

Finally I removed the many-to-many relationship and created a One-to-Many relationship on Date column, and set the cross-filter direction to Single. This could export underlying data from both tables. Then I modified the cross-filter direction to Both, which could export the same results. 

My guess is that a many-to-many relationship would result in a Cartesian product that would result in a lot of matching rows that didn't exist in the original table, so the total number of rows would be much larger than the number of rows of data in the underlying table, so maybe Power BI wouldn't be able to generate and export that data.

Best Regards,
Jing
Community Support Team

[lbendlin]

Semantic models don't have views, they use the term "table".

Is the many to many relationship bidirectional? That would result in a potentially enormous cartesian product.

[elizachan]

Yes, Cross-filter direction is set to both, but report is filtered so the result is big, but not enormous.