Sorting Date Slicer in Power BI

[writetofaiz]

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.

Regards

Faiz

[writetofaiz]

Hi,

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

Is there any other way to handle this scenario?

Regards

Faiz

[v-rzhou-msft]

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.

New table should look like as below.

Finally sory Date column by NewRank column in Report View.

You may refer to this blog 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.

[writetofaiz]

Hi,

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

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

[v-rzhou-msft]

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.

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.

[writetofaiz]

Hi,

In my dataset there are multiple columns.

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

[v-rzhou-msft]

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.

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.

[writetofaiz · 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.

[v-rzhou-msft]

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. 

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.

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.