Forum Discussion
Getting "First" from column without sorting
I have a big dataset which contains of a couple of columns, but the interesting ones are:
- Datetime (YYYY-MM-DD HH:MM:SS) (every row is approximately 10 seconds apart)
- WeekdayName (Monday/Tuesday/Wednesday/...)
- WeekdayNumber (0/1/2/3/...)
When I slice my data in a given data range, all visuals get updated correctly. For clarity purposes I would also like to add on what specific day the slicer starts. For example, if 2023-05-10 is selected, I want it to show in a card below "Wednesday". However, whenever I select the card to show the "First" occurence in this column, it ignores the ordering of the datetime and just returns the first value in alphabetical order. In this case, if there is a Friday somewhere in the daterange, it will always return Friday, as that is the day that comes first in the Alphabet.
The same happens when I show the weekdaynumber. Monday represents 0, so if I take data from Friday to Tuesday, this value will return '0' since 0 is the smallest value in the column in the selected date range. If I select the daterange, then it does work (but then the card show too much information).
I do not want the alphabetical or smallest number of a column, I slice according to a daterange, so I literally want the FIRST value of these columns, specified by the daterange. How do I achieve this, (if this is even possible)? There are multiple values per weekdayName/Number, so creating a seperate sorting table doesn't work in this case.
Try
First Weekday = SELECTCOLUMNS ( INDEX ( 1, 'Table', ORDERBY ( 'Table'[Datetime], ASC ) ), "@value", 'Table'[Weekday Name] )
4 Replies
Try
First Weekday = SELECTCOLUMNS ( INDEX ( 1, 'Table', ORDERBY ( 'Table'[Datetime], ASC ) ), "@value", 'Table'[Weekday Name] )This seems to work good for the start day, thanks!
However, when looking at the last weekday (order by descending) the code takes the last day of the slicer +1. Below an example.
The latest datetime is of the correct date, however the measure doesn't follow this for some reason? 9-6-2023 is a Friday and not a Saturday.
The code that I used for the last day:
Last_Weekday = SELECTCOLUMNS ( INDEX ( 1, 'Table', ORDERBY ( 'Table'[datetime], DESC ) ), "@value", 'Table'[weekdayName] )Check the data in Data view and see what values you have for day of week for that entry.
