Top N Function

[shaildesai]

I used Top N and passed a measure (selected value of a slicer of 1 to 10 values) as the first parameter to make it dynamic. But it is returning no records and showing blank. Any help?

[v-shex-msft]

Hi @shaildesai,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[shaildesai]

Hi @v-shex-msft 

Here is the pbix file: 

https://drive.google.com/file/d/1XhqnWw1WEv20rLXIDLZgPOTbY8IMeatp/view?usp=sharing

I want to see the Top N Orders table where N will be the value selected in the slicer. 

[v-shex-msft · Accepted solution]

Hi @shaildesai,

AFAIK, selectedvalue function is used to get the current selection value, it does not work for multiple selected values. If you need to handle multiple/not selections, I think you need to add the 'alternate Result' parameter in your function to handle multiple values scenarios.

SELECTEDVALUE function - DAX | Microsoft Docs

In addition, current power bi does not support creating dynamic calculated column/table based on filter/slicer selections, they are working on different data levels and you can use child level to affect its parent. I'd like to suggest you use the query parameter and convert it to a table, then you can extract value from that table for expression calculations.

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[amitchandak]

@shaildesai , TOPN should use like

Top3 = calculate(sum(Table1[sales]),TOPN(3,allselected(table[Customer]),sum(Table1[sales]),DESC), values(table[Customer]))

In place of 3 you can use what if

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

Check

TOPN: https://www.youtube.com/watch?v=QIVEFp-QiOk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42