DirectQuery: Use a ranked measure as a column for line chart

Hi bensiqc ,

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:

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn

…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.

As vanessafvg  said, please try to use measure instead.

I have create a sample. Please have a try.

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

another option is to do the rank within the SQL select query

this means your ranking remains the same regardless of how you slice your data

this will show you how to do it the SQL way: How to break a tie using Rank() function in SQL - SQL Server Forums (sqlteam.com)

Best Regards,

Neeko Tang

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