Forum Discussion
Top N Function
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?
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
4 Replies
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
Check
TOPN: https://www.youtube.com/watch?v=QIVEFp-QiOk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42
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
Hi Anonymous
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.
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
