Forum Discussion

Brightsider's avatar
Brightsider
Resolver I
1 year ago
Solved

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

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 TOPNSKI...
  • ahadkarimi's avatar
    ahadkarimi
    1 year ago

    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's avatar
Brightsider
Resolver I
1 year ago

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's avatar
ahadkarimi
Solution Specialist
1 year ago

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.