Top N Lines with direct query table

[Abh_90]

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	G1	BUREAU	XYZ	34484499800130	202201
CTLAA014	G1	BUREAU	XYZ	34484499800130	202202
CTLAA014	G1	BUREAU	XYZ	34484499800130	202204
CTLAA058	G1	BUREAU	XYZ	34484499800130	202201	1010749007840
CTLAA058	G1	BUREAU	XYZ	34484499800130	202201	1960745234422
CTLAA058	G1	BUREAU	XYZ	34484499800130	202202	1010749007840
CTLAA058	G1	BUREAU	XYZ	34484499800130	202202	1960745234422
CTLAA063	G1	BUREAU	XYZ	34484499800130	202201	1010749007840
CTLAA063	G1	BUREAU	XYZ	34484499800130	202202	1010749007840
CTLAA058	G1	BUREAU	XYZ	34484499800072	202111	2640849007191
CTLAA058	G1	BUREAU	XYZ	34484499800072	202111	2880697101827
CTLAA058	G1	BUREAU	XYZ	34484499800072	202111	2900349007380
CTLAA014	G1	BUREAU	XYZ	34484499800056	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 🙏

[v-junyant-msft]

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.

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.

[Abh_90]

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) : 

➡️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]

@Abh_90,

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