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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
writetofaiz
Frequent Visitor

Sorting Date Slicer in Power BI

Hi,

I have a power BI dashboard whose source is a sql query. Including date field, there are other fields in the query. Is there any way we can sort 'Date Slicer ' in Power BI? I tried sorting query on date but It didn't work. You can find the screenshot below that the dates are not sorted properly.

 

writetofaiz_0-1637211991047.png

Regards

Faiz

 

1 ACCEPTED SOLUTION

I have created a seperate date table and joined it with the main table to resolve the issue. Thanks for all your help.

View solution in original post

8 REPLIES 8
writetofaiz
Frequent Visitor

Hi,

 

I cannot convert it to date type since of the values is "Current"

 

writetofaiz_1-1637663460661.png

 

Is there any other way to handle this scenario?

 

Regards

Faiz

 

Hi @writetofaiz ,

Due to you have text value "Current" in this column, this column is in text format. Here I suggest you to add an Index column in Power Query, and then sort this column by Index column. So you need to convert the connection of the table which contains this column from Direct Query to Import. Power BI doesn't support us to add columns in Power Query in table connected by Direct Query. Or you can add an Index column in this table in SQL and load the new table into Power BI.

Firstly add an Index start by 1. Then add a custom column by If function.

1.png

New table should look like as below.

2.png

Finally sory Date column by NewRank column in Report View.

You may refer to this blog for more details.

1.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

Your solution is close to what I am looking for, but when I tried to sort, I got the below error.

writetofaiz_2-1637745022025.png

 

As you can see in the data below, for same event date, there are multiple values in "New Rank" field.

 

writetofaiz_1-1637744952456.png

 

Hi @writetofaiz 

Try Group By function in Power Query.

Firstly Group "All Rows" by [Date] column, sort [Date] column and then add Index column from 1. Then do same steps as above, add an custom column by If to show 0 if [Date] = "Current".

Finally, expand all rows and remove columns you don't need. Result is as below.

1.png

Or you can create a new table without duplicate values like my Table1.

You can download my sample for more details.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

In my dataset there are multiple columns.

writetofaiz_0-1637828558568.png

 

When I grouped by Event date, I got this data and my report went for a toss.

 

writetofaiz_1-1637828725512.png

 

Hi @writetofaiz,

Group "All Rows" by [Date] column, sort [Date] column and then add Index column is to let same dates with the same Index.

Click expand icon in red box and expand all columns you want, then remove columns you don't need and rename the column names. Then you will get the result you want.

1.png

For reference: Work with a List, Record, or Table structured column (Power Query)

You can download my sample above and get more details about expand steps.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I have created a seperate date table and joined it with the main table to resolve the issue. Thanks for all your help.

v-rzhou-msft
Community Support
Community Support

Hi @writetofaiz 

You can select [Date] column and check the Data type in Column Tools. Please check whether your date column in slicer is date type or text type. 

1.png

Here I have a test to get data from SQL by Direct Query and you can see that my [Date] column is in Date type.

You can sort date type column by click "..." icon right above slicer. If your Date column in in date type, it will sort in right way.

2.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors