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

[bensiqc]

I have a request to build out a line graph visual that does the following:

Shows overall performance for all regions as a line

Show a separate line outlining overall performance for only the worst performing region

I have a measure to rank the region:

Rank Region =

IF (

    ISINSCOPE( table[Store Region] ),

    RANKX (

        CALCULATETABLE (

            VALUES ( table[Store Region] ),

            ALLSELECTED ( table[Store Region] )

        ),

        [% Commit Performance]

    )

)

This works to rank each region, then I use my % Commit Performance measure in the graph.

I have tried to adapt this measure as a column in every way I can think of so that I can populate a series based on the lowest rank (7 in this case, but that could change if more regions are added), but I always run into this error: Function RANKX is not allowed as part of calculated column DAX expressions on DirectQuery models.

I understand this error and I understand the performance implications, etc. Regardless, I need to get this done. Can anybody help me devise a solution for this?

[v-tangjie-msft · Accepted solution]

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. 

[vanessafvg]

can you share the code for commit performance? (probably irrelevant)

you need to create a measure, not a calculated column I think.    Calculated column is not permitted.

[bensiqc]

Sure - that measure is below.

The issue I'm having is I think I need the measure in order to assign that ranking initially. RankX is not allowed in a calcluated column, but I do essentially want it as one if at all possible. I have tried a few things like creating a column that essentially just points at that measure but I always receive the error about RankX not being allowed while using DirectQuery (which I need to use).

% Commit Performance = DIVIDE([Commit On Time Packages],[Packages]-CALCULATE([Packages],table[Commit Date] = blank()))