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 entire table data it only queries the top 501. 

How do I just query the entire table / remove TopN? My desired result should be around 83000 rows 

// 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]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      501,
      __DS0Core,
      'Activity'[Activity],
      1,
      'Activity'[CapacityName],
      1,
      'Activity'[LastRefreshTime],
      1,
      'Activity'[Report_BK],
      1,
      'Activity'[User_BK],
      1,
      'Activity'[Date_Creation_BK],
      1,
      'Activity'[Dataset_BK],
      1,
      'Activity'[Group_BK],
      1,
      'Activity'[Time_Creation_BK],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

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]


Thanks a lot,

Mikkel

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

5 Replies

  • 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

      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

        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?