Multicolumn filtering on Table Visual

[ThreeAtion]

Hi Everyone,

We are ThreeAtion and we are working on a report that requires multi column sorting on a table visual. There is table which contains two Columns(CustomerName, OrderDate) that can multiple entries for the same customer.

We tried to implement the multicolumn sorting in the query editor in order to get a customer and its multiple order dates sorted in ascending order. Below is the scenario that we are trying to achieve.

CustomerName    OrderDate

A                           11-03-2019

A                           20-03-2019

A                           09-04-2019

A                           15-04-2019

When we go and implement the sorting in the advance query editor the data gets sorted as desired but as soon as we put this data in a table visual it loses the sorting on the OrderDate and the data looks something like below.

CustomerName    OrderDate

A                           09-04-2019

A                           15-04-2019

A                           11-03-2019

A                           20-03-2019

Request you to please guide on the same. Much Appreciated!

[AnthonyTilley · Accepted solution]

Are your dates stored as actual date formats as the table will usual still sort these correctly if they are dates and not text as shown in your example

unfortunatly the table visual only allows sorting by one colunm

one method to sort is if you are already sorting them in the advanced query editor then apply colunm for row number during this stage you will then be given a new colunm going in the correct order and you can include this in your table and sort on this new colunm to achive your results.

another method is to create a calculated colunm  for the customer + the date with the date in a standard format 

for example

Column = 'Table'[CustomerName    ] & " " & FORMAT('Table'[OrderDate],"yyyy-mm-dd")

this will create a unique colunm that will always sort by customer and then date (when date is in year-month-date format it will sort in the correct order as text)

[ThreeAtion]

Thanks a lot for your input. Yes dates are being stored in actual format and PowerBI is loading them correctly as dates.We will try to follow one of the two alternatives you suggested.