I have a Direct query connection with SAP HANA View. The query used for creating the view sorts the complete data by Date field but i am unable to get sorted data in my desktop report.
Since i am using a direct query mode i do not have any option to sort by column under Modeling. I have created a hierarchy as follows:
Year>month_name>week_number>date . When i drill down to month_name this is what i see.
Any suggestions on how can i sort month names.
Thanks in advance.
I'm not sure, but i think you could put the month number into the field quick info and sort by this value.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Create a Date_Calendar Calculation view and choose Data Category as CUBE. When you will connect this CUBE type Cal View in Power BI Sort by Column option in the Modeling tab will be activated.
You don't need to create any measure in this just keep all the columns as Attribute in Semantics node.
Now you can use Month_Number column to sort Month_Name in this Date_Calendar Cal View.
Here the catch is to create the Date CV as CUBE and not as DIMENSION.
your suggestion is only an option for import mode, not direct query mode, right?
i'm testing here also HANA connectivity with latest versions, and see following problems:
- "Date" Datatype only works in Import noch DQ Mode... Also "Date" is set as "Semantic Type" in the HANA column
- "SortBy" equivalent of PBI is not settable in HANA. Using an "Rank" in HANA View doesn't work
- Only solution working so far in DQ for sorting month Name = Set "Label Column" = Monthname for Column with MonthNO --> User MonthNo column in PBI
In Hana, if you have a Month Number field use the Month Name field as the "Label Column" in your Semantic layer. When it goes into Power BI, Power BI will show your Month Number field with Month Names instead, but will sort by Month Number.
Power BI will also generate a hidden field, Month Number.Key that will contain the month number.
Go to the data tab, choose your table and then go to the modelisation tab and click on Sort By Column : choose you date column and it should work.
The only idea that I have left is to :
Create a view of your table that you are using that will have an OrderBy ASC on Datecolumn
Thanks for such a quick response 🙂
I have tried doing that as well. The HANA view i am connecting with Power BI ,its query uses order by DATE ASC.
Still does not work.
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps