Forum Discussion
DAX: Sorting output
Hello
There is a DAX command to sort an outbut used for Power Automate (ORDER BY).
But it seems not work.
The DAX I am using in Power Automate is:
// DAX Query - Power Automate
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('CQD'[PSTN Trunk FQDN])),
AND(
NOT(
'CQD'[PSTN Trunk FQDN] IN {"(Blank)",
"xx",
"yy"}
)
)
)
VAR __DS0FilterTable2 =
TREATAS(
{"400",
"401",
"701"},
'CQD'[PSTN Call End Reason]
)
VAR __DS0FilterTable3 =
FILTER(KEEPFILTERS(VALUES('CQD'[Total Stream Count])), 'CQD'[Total Stream Count] > 1)
VAR __DS0FilterTable4 =
FILTER(
KEEPFILTERS(VALUES('Query2'[Start Time])),
AND(
'Query2'[Start Time] >= (DATE(@{outputs('Get_Date')}) + TIME(@{outputs('Get_Time_(-x_Mins.)')})),
'Query2'[Start Time] <= (DATE(@{outputs('Get_Date')}) + TIME(@{outputs('Get_Time')}))
)
)
VAR __DS0Core =
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'CQD'[PSTN Trunk FQDN],
'SBC - Information'[Country / Organisation],
'SBC - Information'[Stage],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"SumPSTN_NER_Good_Percentage", CALCULATE(SUM('CQD'[PSTN NER Good Percentage])),
"SumAudio_Good_Stream_Count", CALCULATE(SUM('CQD'[Audio Good Stream Count])),
"SumAudio_Poor_Stream_Count", CALCULATE(SUM('CQD'[Audio Poor Stream Count]))
)
),
AND([SumPSTN_NER_Good_Percentage] < 101, [SumAudio_Good_Stream_Count] > 1)
)
VAR __DS0PrimaryWindowed =
TOPN(501, __DS0Core, 'CQD'[PSTN Trunk FQDN], 1)
EVALUATE
SELECTCOLUMNS(
__DS0PrimaryWindowed, // Use the filtered result set
"SBC", 'CQD'[PSTN Trunk FQDN], // Rename 'PSTN Trunk FQDN' to SBC
"Ctry/Org", 'SBC - Information'[Country / Organisation], // Rename 'SBC - Information'[Country / Organisation] to Ctry/Org
"Stage", 'SBC - Information'[Stage], // Rename
"NER", [SumPSTN_NER_Good_Percentage], // Rename 'SumPSTN_NER_Good_Percentage' to NER
"TOTAL STREAM", [SumAudio_Good_Stream_Count], // Rename 'SumAudio_Good_Stream_Count' to TOTAL STREAM
"TOTAL POOR STREAM", [SumAudio_Poor_Stream_Count] // Rename 'SumAudio_Poor_Stream_Count' to TOTAL POOR STREAM
)
ORDER BY
"[SBC]"Thank you for your help
JFM_12
4 Replies
Hey Heinrich,
Looking at your DAX query, there are a few issues with the ORDER BY implementation that need to be addressed:
Issues Identified:
- Incorrect column reference syntax - You're using "[SBC]" but it should reference the actual column name
- ORDER BY placement - The syntax structure needs adjustment
- Missing sort direction - DAX requires explicit ASC/DESC specification
Solution Steps:
Step 1: Fix Column Reference
- Remove the brackets and quotes around the column name
- Use the actual column name from your SELECTCOLUMNS: SBC
Step 2: Correct ORDER BY Syntax Replace your current ORDER BY line with:
ORDER BY [SBC] ASCStep 3: Alternative Approach (Recommended) Since ORDER BY can be problematic in Power Automate context, consider sorting within the TOPN function instead:
EVALUATE
SELECTCOLUMNS(
__DS0PrimaryWindowed,
"SBC", 'CQD'[PSTN Trunk FQDN],
"Ctry/Org", 'SBC - Information'[Country / Organisation],
"Stage", 'SBC - Information'[Stage],
"NER", [SumPSTN_NER_Good_Percentage],
"TOTAL STREAM", [SumAudio_Good_Stream_Count],
"TOTAL POOR STREAM", [SumAudio_Poor_Stream_Count]
)
ORDER BY [SBC] ASCKey Points:
- Remove quotes and brackets from column references in ORDER BY
- Always specify sort direction (ASC/DESC)
- Test with the TOPN sorting approach if ORDER BY continues to fail
- Power Automate sometimes has limitations with certain DAX functions
Try the corrected syntax first, and if issues persist, implement the TOPN sorting method as it's more reliable in Power Automate environments.
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer
