Setting all Fields in a Table to "Don't Summarize" Doesn't Show Duplicate Rows
Submitted byAnonymouson12-01-202104:10 AM
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.
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.