The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
32 | |
21 | |
16 | |
15 |
User | Count |
---|---|
122 | |
39 | |
30 | |
24 | |
22 |