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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Abh_90
Frequent Visitor

Top N Lines with direct query table

Hello,

 

I have problem for getting Top N Lines from my list with direct query table (PostgreSQL in my case).

Here's an example of my data : 

 

code_ctrl____ CSN  LB_CRT_STRU  co_tide  id_ext_etab            id_month  no_indv
CTLAA014 G1BUREAUXYZ34484499800130  202201 
CTLAA014 G1BUREAUXYZ34484499800130  202202 
CTLAA014 G1BUREAUXYZ34484499800130  202204 
CTLAA058 G1BUREAUXYZ34484499800130  2022011010749007840
CTLAA058 G1BUREAUXYZ34484499800130  2022011960745234422
CTLAA058 G1BUREAUXYZ34484499800130  2022021010749007840
CTLAA058 G1BUREAUXYZ34484499800130  2022021960745234422
CTLAA063 G1BUREAUXYZ34484499800130  2022011010749007840
CTLAA063 G1BUREAUXYZ34484499800130  2022021010749007840
CTLAA058 G1BUREAUXYZ34484499800072  2021112640849007191
CTLAA058 G1BUREAUXYZ34484499800072  2021112880697101827
CTLAA058 G1BUREAUXYZ34484499800072  2021112900349007380
CTLAA014 G1BUREAUXYZ34484499800056  202201 

 

I want to keep only the first 10 lines (in green) based on the id_ext_etab (it's not really top N but keep the first 10 lines soting by id_ext_etab).

Because in my example above when i did a top 2 of id_ext_etab, i get 12 lines but i want just the 2 first lines (even if it's the same id_ext_etab).

 

Constraints : 

1 - This table is in Direct Query Mode, so a lot of functionnalities are not allowed like : 

  • PowerQuery : add index column, keep N first lines...
  • DAX : RANK, RAND, SUMMARIZE...

 

2 - I can't even concatenate columns for perfermance reason (query sent to database will get full data (without filters applied)... so no possible transformation with Power Query).

 

3 - No data Source access/permissions to add index/Rank columns

 

Any help on this please 🙏

 

3 REPLIES 3
v-junyant-msft
Community Support
Community Support

Hi @Abh_90 ,

Please try this way:
When you connect to PostgreSQL, try to use Advanced options and put this code into it:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id_ext_etab ORDER BY id_month) as row_num
    FROM your_table_name
) AS subquery
WHERE row_num <= 10

This is just an example, replace "your_table_name" with the actual name of your table.

vjunyantmsft_0-1706584454650.png

 

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-junyant-msft , @DataInsights 

 

Tha main problem is the GENERATED QUERY by Power BI to be sent to the SGBD (PostgeSQL) each time we consult the data, because as mentioned in my fisrt message, it's a DIRECT QUERY table.

 

When we use SQL statement in Power Query as you suggest : 

Each time we consult the data, Power BI will generate a "non-performing query" to Select, in the first time, ALL the data from your table WITHOUT context filter and then do some subqueries to execute the query you suggest or do any transformation or apply filters or calculted columns...etc ==> so for tables with some millions of rows and same time users access (as its a big company) its a big performance problem !! .

Here's an example of generated query if i just add a DAX calculated column to my fact table (just concatenate 3 columns and use it as link with a dimension table) : 

Abh_90_0-1706782023065.png

➡️First Select Full data (very bad performance) and then subquery for calculating that column and then subquery to apply filters !!!

 

So the idea is to get table in power query without Select statment or any transformation : 

let
    Source = PostgreSQL.Database(PARAM_SERVEUR, PARAM_DB),
    Table = Source{[Schema=PARAM_SCHEMA,Item="Table_Name"]}[Data]
in
    Table

 

I am currently stuck because I need to calculate this column in DAX to make the link with a dimension table, but this will corrupt the generated query as you see in my screenshot and i can't do that in the source (DB) 😔.

any idea please !

 

but by the way i found how to calculate Top N Rows with a DAX measure (no impact on generated query): 

Ranking_Rows = ROWNUMBER(ALLSELECTED(
             'Table Name'[Column 1]
            ,'Table Name'[Column 2]
            ,'Table Name'[Column 3]
            ,'Table Name'[Column 4]
            ,'Table Name'[Column 5]
            ,'Table Name'[Column 6]
            )
    	,ORDERBY(
             'Table Name'[Column 1], desc
            ,'Table Name'[Column 2], desc
            ,'Table Name'[Column 3], desc
            )
)

 

DataInsights
Super User
Super User

@Abh_90,

 

Have you tried custom SQL? You could use the PostgreSQL equivalent of TOPN.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors