cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sotoc Regular Visitor
Regular Visitor

TopN with Direct Query


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

 

IDIndustry
1234567Finance
578512Tech
5135585Tech
124587Healthcare
124587Healthcare
9854257Fitness
412358Restaurants
125874Mechanical
1254774Contractors
36598Spas
8652147Spas
25486Spas
235689Spas
85697Tech
234852Tech
851263Finance
365853Finance

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: TopN with Direct Query

Hi sotoc,

 

To improve the performance, I would recommend you to write SQL query to filter and import the filtered data into power bi firstly because dax engine is much slower than sql engine.

 

Regards,

Jimmy Tao

2 REPLIES 2
Community Support Team
Community Support Team

Re: TopN with Direct Query

Hi sotoc,

 

To improve the performance, I would recommend you to write SQL query to filter and import the filtered data into power bi firstly because dax engine is much slower than sql engine.

 

Regards,

Jimmy Tao

sotoc Regular Visitor
Regular Visitor

Re: TopN with Direct Query

Thanks Jimmy, I appreciate the help. 

 

Carly