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
I have a direct query where I have added a new column from the date field. In this column I have the following formula:
MonthName = SWITCH(MONTH(Query1[Date Raised]),1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")
The problem I have is that it is sorting the data alphabetically, so now December comes before September!!
Any idea how I can put it in numerical order?
Thanks
Solved! Go to Solution.
Hi,
Create a column that is xxx = month(query1[date raised]))
Idk if you already have it already, if you have it, no need to create it :D, and then, you just need to sort the MonthName by the column that gives the month number 😛
I too faced similar challenge of sorting month in Direct query mode of date hierarchy.
So after creating column with Switch function as month function only gives you number of the month create one more column for month number MonthNum.
Sort column according to month number and then add this month to hierarchy and to Visualisation, then table gives you in sorting order.
Note: Remember to sort column MonthNum and then add it hierarchy.
I got your solutions here, hope you like it https://www.youtube.com/watch?v=n_gnuFS8qoY&t=31s
Regards'
Erich Dejesus
Hi,
Create a column that is xxx = month(query1[date raised]))
Idk if you already have it already, if you have it, no need to create it :D, and then, you just need to sort the MonthName by the column that gives the month number 😛
That won't work as I do not have a table view; this is a Direct Query.
Maybe this will help - hopefully:
https://community.powerbi.com/t5/Desktop/DirectQuery-Sort-Data-by-Week-Day/m-p/97676#M41146
FYI, The link from Tom Marten is the correct fix when dealing with a table that load via Direct Query. Which was the problem stated in the OP. The suggested solution is partially right, except that you won't see the table in the Model view. You have to select the column in the table to get the option to Sort by Column to appear.
All working now; the month no. column was what was missing 🙂
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |