Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sotoc
Advocate I
Advocate I

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
v-yuta-msft
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

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

Thanks Jimmy, I appreciate the help. 

 

Carly

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.