Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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)
Proud to be a Super User!
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)
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |