Setting all Fields in a Table to "Don't Summarize" Doesn't Show Duplicate Rows

[Anonymous]

I have a report request to build a simple table in Power Bi to display line level data. While there is a unique key on the table, it has no meaning to the customer so isn't added to the table.

Using direct query and no measures, I have a table that includes an Order Number, Date, Quantity column.

If I add these columns to a table and select all to 'Don't Summarize', the table only shows 1 row for each Order Number, Date, Quantiy combination - it doesn't even Sum the lines so is either an Average or First Summarisation. If I change the Summarization to Sum of Quantity, the Sum value for all the lines is given.

The Date comes from a related dimension table and the other 2 columns are from the Fact table.

When filtering by date in the filters panel, the number of rows next to each date is correct (e.g. next to 01/01/2021 it shows (7) as the number of rows but when selecting that date, only 4 rows appear in the table as 3 have not been shown due to duplicate values).

This table was generated using Direct Query to a SQL Server database.

This makes creating line level tables, something that should be very simple, very unintuitive with big "gotchas" and is in defiance to explicitly setting all columns to 'Don't Summarize' - but it is still summarizing. 

[v-cazheng-msft]

Hi @Anonymous 

Thanks for your feedfack! I basically understand your problem. Normally, these visuals only display unique values. If we would like to display all the values including duplicated values, an extra or index column is needed.

Best Regards,

Community Support Team _Caiyun

[Anonymous]

Hi @v-cazheng-msft 

Thank you for the response. 

It would be very good to have better communication that this is the case. It is still performing a summary on the dataset despite setting columns to 'Don't Summarize' so that would be the expected behaviour.

In general, making tabular reports for users feels very obtuse in Power Bi. My company is transitioning from excel data exports to Power Bi and a common request is to also show the underlying data in tabular form. Making sure rows aren't being aggregated in some way is cumbersome with many pitfalls. Ultimately it leads to user scpepticism and slows our adoption of the platform.

I'm alarmed that this isn't a bug and would like to feedback on the issues when trying to build line-level tables. Please can you add a way for users to easily create line level data to show the tabular data imported to Power Bi, without any explicit or hidden aggregation.

Thanks