DAX: Sorting output

[Heinrich]

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

But it does not work.

What is missing?
Thank you for your help
JFM_12

[Heinrich]

Hello @jaineshp 
Thank you for your extensive explanation
Have a great time
JFM_12

[danextian · Accepted solution]

Hi @Heinrich 

ORDER BY requires a column and not a text.  Remove the double quotes wrapping [SBC]

[Heinrich]

Hello @danextian 
Great it did work.
Have a great time
JFM_12

[jaineshp]

Hey @Heinrich,

Looking at your DAX query, there are a few issues with the ORDER BY implementation that need to be addressed:

Issues Identified:

1. Incorrect column reference syntax - You're using "[SBC]" but it should reference the actual column name
2. ORDER BY placement - The syntax structure needs adjustment
3. 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] ASC

Step 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] ASC

Key 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