Field Parameter when used in filter pane not working for TOPN filters

[sivarajan21]

Hi,

I have created a visual as below:

Now I want to filter this visual by Bottom 10 worst performers by Variance parameter created.FYI the variance Field parameter table shown below:

So I dragged that field parameter column into filter on this visual 'By value'. But its not showing top10 or bottom 10 instead it is showing all site name.

I am struggling to resolve this. Please advise!

PFA file here Portfolio Performance - v2.15 (1) - Copy.pbix

Thanks in advance!

@marcorusso @Greg_Deckler @Ahmedx @Anonymous @Anonymous @tharunkumarRTK 

[tharunkumarRTK · Accepted solution]

@sivarajan21 

You cannot use a parameter column within the topn by field. Follow this syntax and you dont need topn filter with this approach. 

Bottom Performers = 
Var __SelectedMetric = SELECTEDVALUE('Variance'[Parameter Order])
Var __Bottom = SWITCH(
                        __SelectedMetric,
                        0, CALCULATE( [Target Unit Variance] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Target Unit Variance], desc ))),  
                        1, CALCULATE( [Budget Unit Variance] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Budget Unit Variance], desc ))),  
                        2, CALCULATE( [Budget Cost Variance] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Budget Cost Variance], desc ))),  
                        3, CALCULATE( [Target Cost Variance] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Target Cost Variance], desc ))),  
                        4, CALCULATE( [Budget Carbon Variance] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Budget Carbon Variance], desc ))),  
                        5, CALCULATE( [Target Carbon Variance] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Target Carbon Variance], desc ))),  
                        6, CALCULATE( [Target Unit Variance (TimeSeries)] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Target Unit Variance (TimeSeries)], desc )))  ,
                        7, CALCULATE( [Target Cost Variance (TimeSeries)] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Target Cost Variance (TimeSeries)], desc )))  ,
                        8, CALCULATE( [Budget Unit Variance (TimeSeries)] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Budget Unit Variance (TimeSeries)], desc )))  ,
                        9, CALCULATE( [Budget Cost Variance (TimeSeries)] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Budget Cost Variance (TimeSeries)], desc )))  ,
                        10, CALCULATE( [Budget Carbon Variance (TimeSeries)] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Budget Carbon Variance (TimeSeries)], desc )))  ,
                        11, CALCULATE( [Target Carbon Variance (TimeSeries)] ,KEEPFILTERS(TOPN(10, ALLSELECTED(Contacts[Name]), [Target Carbon Variance (TimeSeries)], desc )))  
                        
)
RETURN __Bottom   
                

Please find the attached file. 

Portfolio Performance - v2.15 (1) - Copy (1).pbix

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[sivarajan21]

Hi @tharunkumarRTK ,

Thanks for your amazing response!🙂

This is the gamechanger solution for our report. After so many testing, we have come to a conclusion, this  solution is not efficient as far performance is considered. but we have modified this dax slightly to achieve better timing. Here is screenshot:

I will accept and mark it as solution!

your help means a lot!

Thanks @tharunkumarRTK 😊

[sivarajan21]

Hi @tharunkumarRTK 

Thanks for your quick & amazing response😊

Currently we are testing this file. We will get back to you

Thanks

[lbendlin]

Your sample file is way too big.  Reduce it to the essence of the issue.