Forum Discussion
Top 5 in Rows and Columns
Hi Everyone,
Adding one more situation in the given scenario. (I am using SQL server direct query source hence unable to add new tables in model)
I want to filter category first and then in the filter category I want fetch top 5 associate name and top 5 divison in that particulat category.
Below is the Raw data
| Category | Associate | Division | Spend |
| Business | A1 | Road | 234 |
| Asset | A2 | Services | 343 |
| Electriical | A1 | Home | 223 |
| Labour | A4 | Services | 555 |
| Business | A5 | Group | 4234 |
| Asset | A6 | Medical | 234 |
| Electriical | A7 | Music | 344 |
| Labour | A6 | Home | 2344 |
| Business | A9 | Vehicle | 434 |
| Asset | A6 | Hospital | 434 |
| Electriical | A11 | Class | 2 |
| Labour | A12 | Veg | 23434 |
| Business | A13 | Cream | 234 |
| Asset | A6 | Home | 2344 |
| Electriical | A9 | Vehicle | 434 |
| Labour | A6 | Hospital | 434 |
Asset | A11 | Class | 2 |
Result would be say I filtered Category using page level Filter as Asset. Therfore under asser Top5 supplier and Top 5 spend would be like below snapshot. Mine motive is to segereate the Associate name by category and under cateory which are the Top5 associate and their spend with Top5 division(In column in powerBi)
Kindly help I am struggling on this from last couple of weeks. I am new in PowerBi therefore need support from you.
Regards
Uphar Tandon
Anonymous - Try replacing your ALL statements with ALLSELECTED. ALLSELECTED is kind of tricky but it might work for you. But, the issue is almost certainly your ALL statements.
4 Replies
Well, seems like RANKX and TOPN are out-of-the-question: https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016?view=asallproducts-allversions
So maybe this will help:
https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452
Hi Greg_Deckler
Already gone throught but still unable to create the DAX query or you can say the DAX is not given the correct output.
I am using below DAX but when I am applying Page level Filter (Say in Category as Asset) the result it not accurate.
Please find the DAX
SpendTop5= VAR __topN = 5 VAR __associate = RANKX ( ALL ( 'Table'[Associate] ), CALCULATE( [spend], ALLEXCEPT( 'Table', 'Table'[Associate] ) ), , DESC, DENSE ) <= __topN VAR __division = RANKX ( ALL ( 'Table'[Division] ), CALCULATE( [spend], ALLEXCEPT( 'Table', 'Table'[Division] ) ), , DESC, DENSE ) <= __topN RETURN IF( __division && __associate, [spend] )If you could see, I am applying category filter in my dashboard therefore result is not accurate. I am looking for a DAX that can filter Category and then fetch top5 associate name and their Top5 Division.
Thanks
Uphar
Anonymous - Try replacing your ALL statements with ALLSELECTED. ALLSELECTED is kind of tricky but it might work for you. But, the issue is almost certainly your ALL statements.