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]

amitchandak's avatar
amitchandak
Super User
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.