Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
cinderelly678
Frequent Visitor

Help Removing TOPN

Hello, I am using the following query from performance analyzer to use in a Power Automated flow.

 

Can anyone help re-write this dax so that it does not include TOPN?

 

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({TRUE}, 'Trainee Skill Completions (History)'[Skill Is Active])

VAR __DS0FilterTable2 =
TREATAS({TRUE}, 'Trainee Skill Completions (History)'[Assignement Active (hide)])

VAR __DS0FilterTable3 =
TREATAS({"TES"}, 'Users (Trainees)'[Division])

VAR __DS0Core =
SUMMARIZECOLUMNS(
'Users (Trainees)'[Division],
'Users (Trainees)'[Office],
'Users (Trainees)'[Department],
'Users (Trainees)'[User ID],
'Users (Trainees)'[User Display Name],
'Skills'[Skill Product Line (BU)],
'Skills'[Skill Platform / Group],
'Skills'[Skill Subgroup],
'Skills'[Skill Category],
'Skills'[Skill Title],
'Skills'[Skill ID],
'FY Baseline (hide)'[Baseline Completion Rate],
'Trainee Skill Assignments'[Current Skill Completion Rate],
'Trainee Skill Assignments'[FY Target Percent],
'Trainee Skill Assignments'[FY Goal Status],
'Trainee Skill Assignments'[FY Target Goal Group],
'Trainee Skill Assignments'[Baseline Tasks Required to Goal],
'Trainee Skill Assignments'[Remaining Tasks Required to Certify],
'Trainee Skill Assignments'[Skill Level After Goal],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"Number_of_Requested_Tasks", 'Task Measures'[Number of Requested Tasks],
"Remaining_Mandatory_Tasks", 'Task Measures'[Remaining Mandatory Tasks]
)

VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0Core,
'Skills'[Skill Product Line (BU)],
1,
'Users (Trainees)'[Division],
1,
'Users (Trainees)'[Office],
1,
'Users (Trainees)'[Department],
1,
'Users (Trainees)'[User ID],
1,
'Users (Trainees)'[User Display Name],
1,
'Skills'[Skill Platform / Group],
1,
'Skills'[Skill Subgroup],
1,
'Skills'[Skill Category],
1,
'Skills'[Skill Title],
1,
'Skills'[Skill ID],
1,
'FY Baseline (hide)'[Baseline Completion Rate],
1,
'Trainee Skill Assignments'[Current Skill Completion Rate],
1,
'Trainee Skill Assignments'[FY Target Percent],
1,
'Trainee Skill Assignments'[FY Goal Status],
1,
'Trainee Skill Assignments'[FY Target Goal Group],
1,
'Trainee Skill Assignments'[Baseline Tasks Required to Goal],
1,
'Trainee Skill Assignments'[Remaining Tasks Required to Certify],
1,
'Trainee Skill Assignments'[Skill Level After Goal],
1
)

EVALUATE
__DS0PrimaryWindowed

1 ACCEPTED SOLUTION
talespin
Solution Sage
Solution Sage

hi @cinderelly678 ,

 

Try this

 

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({TRUE}, 'Trainee Skill Completions (History)'[Skill Is Active])

VAR __DS0FilterTable2 =
TREATAS({TRUE}, 'Trainee Skill Completions (History)'[Assignement Active (hide)])

VAR __DS0FilterTable3 =
TREATAS({"TES"}, 'Users (Trainees)'[Division])

VAR __DS0Core =
SUMMARIZECOLUMNS(
'Users (Trainees)'[Division],
'Users (Trainees)'[Office],
'Users (Trainees)'[Department],
'Users (Trainees)'[User ID],
'Users (Trainees)'[User Display Name],
'Skills'[Skill Product Line (BU)],
'Skills'[Skill Platform / Group],
'Skills'[Skill Subgroup],
'Skills'[Skill Category],
'Skills'[Skill Title],
'Skills'[Skill ID],
'FY Baseline (hide)'[Baseline Completion Rate],
'Trainee Skill Assignments'[Current Skill Completion Rate],
'Trainee Skill Assignments'[FY Target Percent],
'Trainee Skill Assignments'[FY Goal Status],
'Trainee Skill Assignments'[FY Target Goal Group],
'Trainee Skill Assignments'[Baseline Tasks Required to Goal],
'Trainee Skill Assignments'[Remaining Tasks Required to Certify],
'Trainee Skill Assignments'[Skill Level After Goal],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"Number_of_Requested_Tasks", 'Task Measures'[Number of Requested Tasks],
"Remaining_Mandatory_Tasks", 'Task Measures'[Remaining Mandatory Tasks]
)

EVALUATE
__DS0Core

View solution in original post

3 REPLIES 3
talespin
Solution Sage
Solution Sage

hi @cinderelly678 ,

 

Try this

 

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({TRUE}, 'Trainee Skill Completions (History)'[Skill Is Active])

VAR __DS0FilterTable2 =
TREATAS({TRUE}, 'Trainee Skill Completions (History)'[Assignement Active (hide)])

VAR __DS0FilterTable3 =
TREATAS({"TES"}, 'Users (Trainees)'[Division])

VAR __DS0Core =
SUMMARIZECOLUMNS(
'Users (Trainees)'[Division],
'Users (Trainees)'[Office],
'Users (Trainees)'[Department],
'Users (Trainees)'[User ID],
'Users (Trainees)'[User Display Name],
'Skills'[Skill Product Line (BU)],
'Skills'[Skill Platform / Group],
'Skills'[Skill Subgroup],
'Skills'[Skill Category],
'Skills'[Skill Title],
'Skills'[Skill ID],
'FY Baseline (hide)'[Baseline Completion Rate],
'Trainee Skill Assignments'[Current Skill Completion Rate],
'Trainee Skill Assignments'[FY Target Percent],
'Trainee Skill Assignments'[FY Goal Status],
'Trainee Skill Assignments'[FY Target Goal Group],
'Trainee Skill Assignments'[Baseline Tasks Required to Goal],
'Trainee Skill Assignments'[Remaining Tasks Required to Certify],
'Trainee Skill Assignments'[Skill Level After Goal],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"Number_of_Requested_Tasks", 'Task Measures'[Number of Requested Tasks],
"Remaining_Mandatory_Tasks", 'Task Measures'[Remaining Mandatory Tasks]
)

EVALUATE
__DS0Core

Thank you @talespin , this worked like a charm! 

@cinderelly678 You're welcome.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.