I am using Direct Query with up to several million rows. I am having issues creating a visual of Top 5 Industry Codes by count by highest number of Unique IDs.
I need to filter out duplicate Codes before I filter on industries, so the Unique IDs count is calculated by this measure:
Measure for # Unique IDs =
var dc = Calculate (DISTINCTCOUNT('Table'[UniqueID]),Filter('Table'[UniqueID] <> BLANK ()))
return IF(ISBLANK(dc), dc, dc)
Then I am using the filter pane on a bar chart to find the Top 5 by count of Unique IDs. It is causing my page to load very slowly ... I imagine a measure will make this more efficient but when I try to use TopN function I can't get the measure right when using Direct Query.
I prefer to do this within the report with my original table, but as I understand it, I also have these options and need to know which will is best for performance:
-- Write a SQL query when connecting to the db to create a separate view/table in my report
-- Duplicate the main table after import, delete the rows I don't need and go to Home --> Keep Rows --> Top 5
| ID | Industry |
| 1234567 | Finance |
| 578512 | Tech |
| 5135585 | Tech |
| 124587 | Healthcare |
| 124587 | Healthcare |
| 9854257 | Fitness |
| 412358 | Restaurants |
| 125874 | Mechanical |
| 1254774 | Contractors |
| 36598 | Spas |
| 8652147 | Spas |
| 25486 | Spas |
| 235689 | Spas |
| 85697 | Tech |
| 234852 | Tech |
| 851263 | Finance |
| 365853 | Finance |
Image of what I need and the current filter:
https://www.dropbox.com/s/4luq2aektl8lbn4/TopN_DirectQuery.jpg?dl=0
Help is much appreciated!
Carly
