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  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 🙏

 

Need Help

3 Replies

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