Data order same as the Original Query

[rocky09]

I am using Direct Query method to pull data from Oracle Server. 

In the Original Query, I have sorted few columns and in Query Editor, I can see the same order as in the Original Query. But, when I drag all the fields into a table report. I am not getting the data order as in the Original Query.

Can anyone advise, how can i get the data order as in the original query.

[TomMartens]

Hey,

I'm not sure what you mean by you don't get the same data order, I guess you are referencing the order of the rows.

The effect can be one of these

• Unique Rows
  The Rows in the table are not unique, this means that numeric values will be aggregated -> 100 rows in the source table will be aggregated to 97 rows in the Power BI table visual
• Implicit Ordering of non numeric columns

I guess you have to consider the following

• Create a measure that sorts the rows accordingly using RANKX

Depending of the number of rows, ordering can be slow.

Regards,

Tom

[rocky09]

Thanks Tom,

yes, i am referring to "order of the rows".

I guess, RankX won't work in Direct Query Mode.

[TomMartens]

Hey,

according to this list:

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-d...

RANKX is one of the not optimized functions (for direct query), I just tested it and it works. I have no idea how this will behave from a performance perspective.

Regards,

Tom

[rocky09]

Thank you again,

but, not working. I am getting the error that RANKX cannot be used on Direct Query.

[rocky09 · Accepted solution]

I found a solution.

What I did was, I have created a custom column and Concatenated the required columns then put that custom column and sorted.

[TomMartens]

Hey, this is cool.

Maybe this post is of interest:

https://community.powerbi.com/t5/Desktop/What-Happened-To-quot-Allow-unrestricted-measures-in-Direct...

prior to the July release there has been an option to allow unrestricted measures (meaning allow more DAX functions in measures) using direct query.

Maybe you want to check if you have the "preview" feature "Composite Models" enabled.

Options -> Preview Features.

I tested RANKX in direct query mode against a SQL Server table without an error message.

Regards,

Tom