cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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.

 

 Capture.PNG

 

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.

9 REPLIES 9
mwegener
Super User II
Super User II

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Raman
New Member

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

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

 

MegaSuper1
Regular Visitor

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
Not applicable

Thanks for your response. 🙂

Can you please explain this with an example or screenshots 

 

Regards

Power BI User

quentin_vigne
Solution Sage
Solution Sage

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
Not applicable

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

 

@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
Not applicable

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.

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors