Forum Discussion
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
__DS0CoreORDER 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
- amitchandakSuper User
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
__DS0CoreORDER 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]- MIkkelHyldigHelper 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?- richardburlingHelper 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?