Sorting by Filter

[stefantaust]

Hello, is it possible for me to sort the data field CheckP ID in ascending order using the filter? Thanks Stefan

[danextian]

Hi @stefantaust 

Ready your visual - filter it by a measure, sort the values and remove filters that you don't want to be applied to when running a query against a dataset in Power Automate.

Go to Optimize tab, run performance analyzer, hit the refresh button in the analyzer pane and click Run in DAX query view.

The above visual will generate the below DAX query code. Note both Category and Month Short columns are custom sorted so their custom sort columns will appear in the query.

DEFINE
	VAR __ValueFilterDM1 = 
		FILTER(
			KEEPFILTERS(
				SUMMARIZECOLUMNS(
					'Geo'[Geo],
					'Dates'[Month Short],
					'Dates'[Month Number],
					'Category'[Category],
					'Category'[sort],
					"Total_Revenue", '__MEASURES'[Total Revenue]
				)
			),
			[Total_Revenue] < 250000
		)

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL(
				ROLLUPGROUP(
					'Geo'[Geo],
					'Dates'[Month Short],
					'Dates'[Month Number],
					'Category'[Category],
					'Category'[sort]
				), "IsGrandTotalRowTotal"
			),
			__ValueFilterDM1,
			"Total_Revenue", '__MEASURES'[Total Revenue]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			502,
			__DS0Core,
			[IsGrandTotalRowTotal],
			0,
			'Category'[sort],
			0,
			'Category'[Category],
			0,
			'Geo'[Geo],
			1,
			'Dates'[Month Number],
			1,
			'Dates'[Month Short],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal] DESC,
	'Category'[sort] DESC,
	'Category'[Category] DESC,
	'Geo'[Geo],
	'Dates'[Month Number],
	'Dates'[Month Short]

The above code will be simplified as below:

DEFINE
	VAR __ValueFilterDM1 = 
		FILTER(
			KEEPFILTERS(
				SUMMARIZECOLUMNS(
					'Geo'[Geo],
					'Dates'[Month Short],
					'Dates'[Month Number],
					'Category'[Category],
					'Category'[sort],
					"Total_Revenue", '__MEASURES'[Total Revenue]
				)
			),
			[Total_Revenue] < 250000
		)


EVALUATE
	__ValueFilterDM1

ORDER BY
	
	'Category'[sort] DESC,
	'Category'[Category] DESC,
	'Geo'[Geo],
	'Dates'[Month Number],
	'Dates'[Month Short]

Use the above in running a query against a power bi dataset.

[stefantaust]

i need a filter that sort the column "ID" in ascending order

[speedramps]

Hi again Stefan

I want to help you but your description is too vague. Please write it again.

If you dont know how to write a clear decsription then ask your manager, a collegue, teacher, parent, relative or friend to help you.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[stefantaust]

Hello, I have a Power Automation button that I use to export filtered data as an Excel file in SharePoint. It's working fine so far. However, unfortunately, the exported data isn't in the desired order. Regards, Stefan

[speedramps]

It is acsending oirder

CID0001, CID0002, CID0003, CID0004, CID0005 

I want to help you but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.