"The TOPNSKIP function incompatible with DirectQuery", but my entire model is Import only?

[Brightsider]

So, I'm trying to implement some calculation items as reporting controls to allow me to only show results for the 2nd Highest or 3rd Highest user according the the result of some measure. The TOPNSKIP function seemed like the perfect thing to make this happen:

EVALUATE
VAR _measureresultstable = 
FILTER(
	ADDCOLUMNS(
		ALLSELECTED('System Users'),
		"MeasureResults",
		SELECTEDMEASURE()
	),
	NOT(ISBLANK([MeasureResults]))
)

VAR _OurNumberTwo = 
TOPNSKIP(
    2,
    1,
    _measureresultstable,
    [MeasureResults], DESC
)
RETURN
	_OurNumberTwo

However, when I run the function in the DAX Query view, or when I try to create it as a measure, I get the following error:

The TOPNSKIP function is incompatible with the DirectQuery data source, as it does not support skipping rows before returning the result rows.

But, the entirety of my data model, including the tables I am using in the calculation item, are all set to Import mode. I don't have a single DirectQuery data source in my entire model. What explains why TOPNSKIP would not function?

[ahadkarimi · Accepted solution]

Hi @Brightsider, give this a try, and if you encounter any issues, let me know.

EVALUATE
VAR _measureresultstable = 
FILTER(
    ADDCOLUMNS(
        ALLSELECTED('System Users'),
        "MeasureResults", SELECTEDMEASURE()
    ),
    NOT(ISBLANK([MeasureResults]))
)

VAR _RankedTable =
ADDCOLUMNS(
    _measureresultstable,
    "Rank", RANKX(_measureresultstable, [MeasureResults], , DESC)
)

VAR _OurNumberTwo = 
TOPN(
    1,
    FILTER(_RankedTable, [Rank] = 2),  -- Change 2 to 3 for 3rd highest
    [MeasureResults], DESC
)

RETURN
    _OurNumberTwo

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!

[Brightsider]

Thank you again for the prompt reply, @ahadkarimi! One thing I did want to know is: why does the RANK/RANKX approach work fine, but TOPNSKIP can't deliver the goods?

[ahadkarimi]

Hey @Brightsider,
RANKX works well because it ranks rows without skipping any, making it fully compatible with Import mode. On the other hand, TOPNSKIP involves skipping rows, which the DAX engine restricts due to its usual incompatibility with DirectQuery scenarios, even if your model is in Import mode.