Rankx in direct query

[Anonymous]

Hi all,

Im using Amazon redshift as my data source connected in Power bi through direct query mode. When I'm using rankx function for creating a calculated column I'm getting error like rankx function no supported in direct query.

My question was How to implement rankx function for a creating a caluclated column in direct query?

Please help me on this.

Thanks

Ganesh

[Anonymous · Accepted solution]

Hi @Anonymous ,

Whenever you get an error message like “Function ‘RANKX’ is not supported in DirectQuery mode” you know you’re trying to use a DAX function that isn’t allowed by default in DirectQuery mode.

Why does this limitation exist? Well, as the documentation points out here:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery#limitations-of-directquery

…this is done for performance reasons. In Power BI, every time you interact with a report (by opening it, clicking on a slicer etc) a series of DAX queries is sent back to the Data Model to get the data needed by the report. In DirectQuery mode, all of these DAX queries – including any calculations – are translated into queries that are sent back to the data source. If that data source is SQL Server, then Power BI generates SQL queries. Not everything that you can do in a DAX calculation can be translated back into efficient SQL, so to prevent you from accidentally building calculations that will perform badly in DirectQuery mode Power BI tries to prevent you from using any DAX functions that it thinks it can’t generate efficient SQL for.

Please try to use measure instead.

I have create a sample. Please have a try.

Measure = RANKX(ALL('Sheet1$'),[profitttt],,DESC,Dense)

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Are you trying to create a new column. I think that is not supported. You can create a measure

For Rank Refer these links - First one is measure Rank, second one is column rank
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns