Direct Query RankX Top 25/Other

[Anonymous]

I'm currently using Direct Query to load my data and create a donut chart showing supplier group name and company id (as the count)

My desired result would be the top 25 ranked companies & then all others classified as "Other"

Upon my research I have found that this function might not be supported in Power Bi , is this true?https://community.powerbi.com/t5/Desktop/Rank-in-direct-query-mode/m-p/97273#M40979

My current format is to create DAX measures:

RANK =

RANKX(ALL('looker_views lkr_Intake_Data'), SUMX('looker_views lkr_Intake_Data', 'looker_views lkr_Intake_Data'[Company_Id]))

RANK IF = IF (

AND (

NOT ( ISBLANK ( 'looker_views lkr_Intake_Data'[Supplier Group Name] ) ),

ISFILTERED ( 'looker_views lkr_Intake_Data'[Company_Id] )

),

RANKX ( ALL ( 'looker_views lkr_Intake_Data'[Company_Id] ),'looker_views lkr_Intake_Data'[Supplier Group Name] )

)

I've also  tried to enable the restrictions but to no avail

go to file->options and settings -> options

under direct query, select "allow unrestricted measures"

Can anyone help?

[Anonymous]

RANKX is not supported in DQ mode:

https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc...

If you look for this function using CTRL+F, you won't find it.

But RANKX can be calculated using standard DAX functions and filters and such calculations most of the time are FASTER than using RANKX anyway.

Please read this:

https://www.sqlbi.com/articles/how-to-compute-index-numbers-at-top-speed/

Best
D

[Anonymous]

Also, for dynamic calculations with the Other member, please read this:

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

Best
D