Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am trying to rank the performance of my Districts based on their YoY Growth using RANKX().
Current formula, where Custom is a flag to exclude specific districts:
if( hasonevalue(Local[DISTRICT]), calculate(rankx(FILTER(all(Local),Local[Custom]=0),FPins[PINS YTD YoY Δ]),ALLEXCEPT(Local,Local[DISTRICT])), "-")
However, my current formula doesn't work. It needs to exclude Districts with "OTHER" in their name. Also, the Ranking must stay valid when I filter the table visual. Let's say I only keep North America Districts via a filter, the ranking of the district must stay the same.
Any ideas ?
Thanks.
Solved! Go to Solution.
Sorry, my bad, FILTER can only accept one-two arguments, try the below instead.
VAR _search = "OTHER" VAR _tbl = CALCULATETABLE( FILTER( ALLSELECTED( Local[DISTRICT] ), SEARCH( _search, Local[DISTRICT], 1, 0 ) = 0 ), Local[Custom] = 0 ) RETURN IF( SEARCH( _search, SELECTEDVALUE( Local[DISTRICT] ), 1, 0 ) = 0, RANKX( _tbl, [PINS YTD YoY Δ] ) )
Sure, try something like below.
VAR _search = "OTHER" VAR _tbl = FILTER( ALLSELECTED( Local[DISTRICT] ), SEARCH( _search, Local[DISTRICT], 1, 0 ) = 0, Local[Custom] = 0 ) RETURN IF( SEARCH( _search, SELECTEDVALUE( Local[DISTRICT] ), 1, 0 ) = 0, RANKX( _tbl, [PINS YTD YoY Δ] ) )
Sorry, my bad, FILTER can only accept one-two arguments, try the below instead.
VAR _search = "OTHER" VAR _tbl = CALCULATETABLE( FILTER( ALLSELECTED( Local[DISTRICT] ), SEARCH( _search, Local[DISTRICT], 1, 0 ) = 0 ), Local[Custom] = 0 ) RETURN IF( SEARCH( _search, SELECTEDVALUE( Local[DISTRICT] ), 1, 0 ) = 0, RANKX( _tbl, [PINS YTD YoY Δ] ) )
Thank you so much!
I have adjusted the formula by switching ALLSELECTED to ALL, because my tables must only show a specific region District.
The "OTHER" districts are removed and not included in the Ranking, which is perfect.
There is a blank District, which might be removed using a filter excluding the blanks districts.
Just a final question. If I want the Rank to be a specific value (like "-") when more than one district is displayed, where should I put the if(hasonevalue() statement ?
Thanks again!
Edit: Nevermind, I had a discrepant value in another table creating the blank. Solved it.
Hi @Mariusz ,
thank you for helping. So what you do is create two variables, one that search valid districts, and one that ranks the PINS YTD YoY Δ based on a table of valid districts ?
However, I have a slight issue, Filter refuses to accept the Local[Custom] = 0 argument. Is it needed since you have already the Search function looking for 0 ?
Thanks,