Forum Discussion

Abh_90's avatar
Abh_90
Frequent Visitor
2 years ago

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  ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Abh_90
Frequent Visitor
2 years ago

Hello Anonymous , 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
            )
)