Remove TOPN from query

[MIkkelHyldig]

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

[amitchandak · Accepted solution]

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

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]

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]

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]

@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?