Forum Discussion
"The TOPNSKIP function incompatible with DirectQuery", but my entire model is Import only?
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 _OurNumberTwoDid I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!
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
_OurNumberTwoDid I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!
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?
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.
