Forum Discussion
KarlinOz
Advocate III
9 years agoData view - date display formats
I seem to be running into a problem I was previously having here: https://community.powerbi.com/t5/Desktop/Default-sort-column-of-table-in-report/td-p/63901 I thought I had it solved there but I...
KarlinOz
Advocate III
9 years agoOK so I have taken one step forwards and have two steps to go...
It turns out that the reason I was getting the "We cannot sort DayDateTime by DateTime..." error is because in my source column I have dd/MM/YYYY hh:mm:ss but in my target I had not declared the :ss (seconds). DayDateTime = FORMAT([DateTime], "ddd dd/MM/YYYY hh:mm tt") results in the "We cannot sort..." error but DayDateTime = FORMAT([DateTime], "ddd dd/MM/YYYY hh:mm:ss tt") allows me to sort by the other column. It appears that you have to have exactly the same number of elements in source and target. That makes sense I guess but it then works against what I want to acheive.
I can now sort my target column, which includes my day abbreviation. However I now have two issues:
- I want to disply the DayDateTime without seconds however when I remove seconds from my FORMAT statement I can not sort DayDateTime by DateTime. My source data has seconds included and so I need to include it in the targe column but I don't want to display seconds - how can I remove them from the display?
- According to here: http://social.technet.microsoft.com/wiki/contents/articles/685.power-bi-dax-text-functions.aspx#predefined_datetime "tt" in the statement "displays an uppercase AM with any hour before noon; displays an uppercase PM with any hour between noon and 11:59 P.M". I am in a Locale which uses AM/PM format and I have checked the file options to make sure the Locale is set correctly (Australia) so why does it display as this?:
for this statement: