How to show Month Name in sorted order when connecting with SAP HANA Views in direct query mode

[Anonymous]

Hi,

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.

[mwegener]

Hi,

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.

[Raman]

Try this...

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.

Reagrds

Raman

[jochenj]

Hi Raman, 

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

[Anonymous]

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.

[Anonymous]

Thanks for your response. 🙂

Can you please explain this with an example or screenshots 

Regards

Power BI User

[quentin_vigne]

Hi @Anonymous

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.

- Quentin

[Anonymous]

Hi Quentin,

Since i am using a direct query mode , sort by column option is not available.

Is there any other way to do this.

Thanks

[quentin_vigne]

@Anonymous

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

- Quentin

[Anonymous]

Hi Quentin,

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.