topic Re: "The TOPNSKIP function incompatible with DirectQuery", but my entire model is Import only? in DAX Commands and Tips

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

Brightsider — Thu, 29 Aug 2024 17:05:32 GMT

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?

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

ahadkarimi — Thu, 29 Aug 2024 17:18:24 GMT

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!

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

Brightsider — Thu, 29 Aug 2024 17:21:26 GMT

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?

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

ahadkarimi — Thu, 29 Aug 2024 17:30:53 GMT

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.