Forum Discussion
top n by dynamic
I have a table with three fields: sales, customer, and qty .
p_TOPN = DATATABLE (
"index", INTEGER,
"TOPN", STRING,
{
{ 1,"TOP1" },
{ 2,"TOP2" },
{ 3,"TOP3" }
})
rank_by meature value
rank_by = SWITCH(TRUE(),
ISFILTERED(Sheet1[sales]),RANKX(ALL(Sheet1[sales]),[qtySum]),
ISFILTERED(Sheet1[customer]),RANKX(ALL(Sheet1[customer]),[qtySum]),
BLANK())
I am now able to rank sales and customer sales separately.
p_muti = {
("sales", NAMEOF('Sheet1'[sales]), 0),
("customer", NAMEOF('Sheet1'[customer]), 1)
}
I hope to dynamically switch between res1 and res2 through field parameters p_muti
TOP_dynam =
var n = SELECTEDVALUE('p_TOPN'[index])
var tab1 = ADDCOLUMNS( DISTINCT('Sheet1'[sales]),"rank",[rank_by],"qtySum",[qtySum])
var tab2 = ADDCOLUMNS( DISTINCT('Sheet1'[customer]),"rank",[rank_by],"qtySum",[qtySum])
var res1 = CALCULATE(FIRSTNONBLANK('Sheet1'[sales],'Sheet1'[sales]),FILTER(tab1,[rank]=n))
var res2 = CALCULATE(FIRSTNONBLANK('Sheet1'[customer],'Sheet1'[customer]),FILTER(tab2,[rank]=n))
return res1
result
gitee link PBI file
baidu netdesk link:https://pan.baidu.com/s/1C8C4EzhcUoOz7ztuOYNKsQ
code:fllx
githubfast link PBI file
2 Replies
- amitchandakSuper User
JohnYU , refer if this can help
Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
Get Super Dynamic TOPN using Window, Numeric, and Field parameters along with calculation groups- https://youtu.be/YW1SOk9V9xk
- JohnYUFrequent Visitor
amitchandak I have implemented your dynamic ranking through field parameters.
What I need to achieve is a reverse query field through ranking,
such as when choose sales, then top_dynamic shows sa1,sa2 etc
when choose customer, then top_dynam shows customer1,customer2, etc.
see video for detail githubfast link video and pbi file