Filter DISTINCT values in specific column after user interaction with bar chart

[Anonymous]

Hello,

I have a large SQL server database that consists of 60 million rows that I have imported using DirectQuery. This table contains duplicate records in one specific column that I would like to hide from a table view in my report. This large 'Detail' table is connected to a smaller 'Header' table with a many to one relationship using an ID field. 

In my report I would like to add a feature that lets the user select a bar from a bar chart with 'Header' table data which would filter a subset of the 'Detail' table. This subset of data will be shown as a table in my report. This works fine except that one column has duplicates which I would like to filter. In the below scenario I would like to filter the third and fourth row that has a duplicate ID and Task combination. I am importing the data using DirectQuery so I cannot remove duplicates using the Query Editor. 

Any help is greatly appreciated!

After selecting the bar with ID 386714 the connected Detail table shows all records with this ID.

ID           Task                Duration

386714	start	0
386714	write_invoice	15
386714	write_invoice	15
386714	write_invoice	15

[v-diye-msft]

Hi @Anonymous ,

You can refer to the same case for reference: https://community.powerbi.com/t5/Desktop/Remove-duplicates-in-direct-query/td-p/238430

Best regards,
Dina Ye

[CheenuSing · Accepted solution]

Hi @Anonymous ,

Have you tried filtering the duplicates in direct query.  Ideally you should use a View which will remove duplicate records and use that as DirectQuery for your loading.

Cheers

CheenuSing