Export underlying data is Returning rows that should be filtered out.

[rossnruthie]

We have set up a model with two tables: Seller and Transactions.  We are using the Seller from the seller table as a report filter to filter out rows in the transactions table.  The transaction table has a single SUM measure that we default to 0 to avoid gaps in our visuals.

SumMeasure = SUM(Transacting) + 0

SumMeasure = IF(ISBLANK(SUM(Transacting)),0,SUM(Transacting))

 Everything looks good, but after uploading and using the Export Underlying Data option I get back and excel file with the filtered seller information AND all other sellers with "0" as the value of the measure.  this is obviously not ideal.  I did some messing around and was able to reproduce how i think the export is functioning by creating a table visual with the seller from transaction table.  When I use the seller from the seller table(as I am in my visual), I get a single row as expected.  When i swap the seller out with the seller from the transaction table I get the same results that are in my export file.  I don't get this behavior as I would expect to have the same result no matter which seller I'm using as they have a direct relationship.  I've uploaded a sample HERE.

Can someone tell me why this is happening and if possible how I can fix the export to not export all other sellers with 0's?

[v-sihou-msft]

@rossnruthie

This is because you give the 0 value in your SumMeasure.

By default, Power BI table visual will always remove the entries with empty value. In your scenario, you give 0 as default so that all Sellers in Transacting Table has value.

Just remove the 0 in your measure, it will only show one Seller from Transacting.

Regards,

[rossnruthie]

I understand that I'm giving a default value of 0, that is by design.  I need to assign 0's because I want to see companies that have legitimate values of 0.  But what I don't understand is why the filter that I have set up on the seller is not removing companies from the table but is instead displaying them and making their value 0.  I would expect the filter to remove the sellers from the table view altogether.

For example, with no filters I get all data which is expected..

I put a report filter that uses the org from the seller table for "MyCompany01" which has a relationship to the transacting table on Seller.  Rather than removing all sellers that aren't "MyCompany01", it just 0's out all of the other values.

This is the same exact behavior that is happening when exporting underlying data and is causing a huge headache.