topic Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode in DAX Commands and Tips

Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

TickData — Wed, 23 Apr 2025 12:15:38 GMT

Has anyone had issues ranking using Direct Query vs import modes? I am using the following measure which works when the table is imported but not in direct query mode:

Rank Test = 
    RANKX(
        FILTER(
            ALL(
                accounts[last_program_size], 
                accounts[program_size]
                ),
            accounts[last_program_size] = MAX(accounts[last_program_size])
        ),
        CALCULATE(
            sum(accounts[count_flag])
            )
    )

A number of the functions used have this remark in the official documentation but I am not using it in a calculated column and do not have RLS configured.

This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Thanks.

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

lbendlin — Wed, 23 Apr 2025 13:43:20 GMT

Try using the RANK function instead.

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

TickData — Wed, 23 Apr 2025 14:42:11 GMT

lbendlin I seem to get an error when I do it like this:

Rank Test A =

RANK(

DENSE,

ORDERBY(sum(accounts[count_flag]), DESC),

PARTITIONBY(accounts[last_program_size])

)
Message:
RANK's Relation parameter was omitted and its ORDERBY clause contained a non-column reference expression. This is not supported.

I guess this makes sense as I need to order by sum(accounts[count_flag]) not a column on its own.

If it helps this needs to go into a table visual and display the last_program_size, program_size, sum(accounts[count_flag]) and the rank. It needs to be dynamically calculated as filters are applied in the model.

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

lbendlin — Wed, 23 Apr 2025 18:00:19 GMT

you can materialize the measure values per [last_program_size] (i guess that is your ranking target) into a table variable and then run RANK over that table.

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

TickData — Thu, 24 Apr 2025 11:02:04 GMT

Is this how you mean?

Rank Test wih Table =

VAR grouped_table =
SUMMARIZE(accounts,
accounts[last_program_size],
accounts[program_size],
"No of Next Purchases", COUNT(accounts[id])
)

RETURN

RANK(
DENSE,
ORDERBY(grouped_table[No of Next Purchases]),
PARTITIONBY(grouped_table[last_program_size])
)

If I do it like this the columns in ORDERBY and PARTITIONBY are underlined and I get the error:

Cannot find table 'grouped_table'.

Any ideas what I am doing wrong?

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

lbendlin — Thu, 24 Apr 2025 13:35:58 GMT

put the table reference in the relation parameter. Then in the orderby you can reference the column name.

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

TickData — Thu, 24 Apr 2025 14:44:15 GMT

Thanks. I've done it as below but it's still ranking everything as 1.

Rank Test wih Table =

VAR grouped_table =

SUMMARIZE(accounts,

accounts[last_program_size],

accounts[program_size],

"No of Next Purchases", COUNT(accounts[id])

)

RETURN

RANK(

DENSE,

grouped_table,

ORDERBY([No of Next Purchases], DESC),

PARTITIONBY([last_program_size])

)

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

lbendlin — Thu, 24 Apr 2025 15:12:01 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

v-achippa — Mon, 28 Apr 2025 07:36:34 GMT

Hi TickData,

Thank you for reaching out to Microsoft Fabric Community.

Thank you lbendlin for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? If not please provide sample data that covers your issue or question completely.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

v-achippa — Fri, 02 May 2025 13:25:33 GMT

Hi @TickData,

We wanted to kindly follow up to check if the solution provided by the super user resolved your issue? If not please provide sample data that covers your issue or question completely.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

v-achippa — Mon, 05 May 2025 11:26:51 GMT

Hi @TickData,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue?

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

Re: Ranking with RANKX, FILTER, ALL & CALCULATE not working in Direct Query mode

v-achippa — Fri, 30 May 2025 08:01:14 GMT

Hi TickData,

As we haven't heard back from you, we are closing this thread for now. If you have any further issues, please start a new thread in the community forum, and we will be ready to assist you. Thank you for being part of the Microsoft Fabric Community.

Thanks and regards,

Anjan Kumar Chippa