Forum Discussion

MIkkelHyldig's avatar
MIkkelHyldig
Helper II
4 years ago
Solved

Remove TOPN from query

Hi guys, For Power Automate I need to run a query for a table and therefore I copy my query from performance analyzer in Power BI from the table I need.  However, instead of returning the entir...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    MIkkelHyldig , Try like

     

    // DAX Query
    DEFINE
    VAR __DS0Core =
    SUMMARIZE(
    'Activity',
    'Activity'[Activity],
    'Activity'[CapacityName],
    'Activity'[LastRefreshTime],
    'Activity'[Report_BK],
    'Activity'[User_BK],
    'Activity'[Date_Creation_BK],
    'Activity'[Dataset_BK],
    'Activity'[Group_BK],
    'Activity'[Time_Creation_BK]
    )


    EVALUATE
    __DS0Core

    ORDER BY
    'Activity'[Activity],
    'Activity'[CapacityName],
    'Activity'[LastRefreshTime],
    'Activity'[Report_BK],
    'Activity'[User_BK],
    'Activity'[Date_Creation_BK],
    'Activity'[Dataset_BK],
    'Activity'[Group_BK],
    'Activity'[Time_Creation_BK]

MIkkelHyldig's avatar
MIkkelHyldig
Helper II
4 years ago

Thanks amitchandak ,

It worked. I tried to remove the variable "TopN" and the ORDER BY but got an error. Is the solution to keep the ORDER BY function?

richardburling's avatar
richardburling
Helper I
4 years ago

Yes, I believe you need to keep the Order By function. 

 

I had a similar query and removed the TOPN function, the only issue I have is that the query will only extract 10,000 rows. Unfortunately my query needs 1,000,000+ rows. 

 

Does anyone have any solutions? 

  • AlLangstaff's avatar
    AlLangstaff
    Frequent Visitor
    3 years ago

    Old, but in case anyone is here looking for answers. You can just drop these queries into PowerBI Report builder to quickly and easily write the DAX for a Paginated Report. No row limit then.

    • MarkPalmberg's avatar
      MarkPalmberg
      Kudo Commander
      1 year ago

      AlLangstaff what does this mean?

      AlLangstaff wrote:

      Old, but in case anyone is here looking for answers. You can just drop these queries into PowerBI Report builder to quickly and easily write the DAX for a Paginated Report. No row limit then.

      Drop from where?