Top N not working in the visual filter in my old report

[BBIUser]

Hello Community members,

I am having issues with Top N in the Visual level filter. This is a report which was built 2 years back. So, I am using the same report due to some enhancements. For the existing issue, there was no changes made either in the report design nor in the calculated 'Measures' except changes in the underlying data. Also, the Top N was built using the old filter Power BI feature.

Here is my requirement - 

1] Actual Dataset (Fields from the Query)

  

2] I have to show the Top 3 Medical Validations (%) in a table visual. Screenshot shown below. Logic is 850/967 *100 = 87.90%.

To build this logic in Power BI, I have used a calculated column and calculated measures. Refer below.

ColumnCalc = SUM('MedChecks'[Med_Count])

MeasurefrMedCount = CALCULATE(SUM('MedChecks'[Med_Count]))

MedValid% = CALCULATE(DIVIDE('MedChecks'[MeasurefrMedCount], VALUES('MedChecks'[ColumnCalc])))

To show only Top 3 values from the Med valid%,

> I had used "Med Count" field in the Visual Filter

> applied Top N = 3 and then

> By value = MedValid%

Problem is, when I use the Top 3 with above values in Top N visual filter, the % value changes (less than the exsiting value for all the records and also does not show only the Top 3. Any idea why it is behaving like this?

Please remember, there are no changes except the underlying data and another point is I am using the same calculations, logic and design in other old report and it is working fine.

Thank You!

[BBIUser]

Here is another solution I tried with RANKX,

RANKTop3 = RANKX (ALL('MedChecks'[Med_Code]), CALCULATE(SUM('MedChecks'[Med_Count])))

Applied the RANKTop3 to the visual filter,

> selected is less than or equal to 3 

Top 3 works in this case but when I use the URL field (which has the web page links) or any other dimensions within the table visual the Top 3 does not support and it shows all the records. This is not the case in my other report as I am using the URL field along with Med Code and Med Valid % fields.

[Ashish_Mathur · Accepted solution]

Hi,

Does this work

RANKTop3 = RANKX(CALCULATETABLE(ALL('MedChecks'[Med_Code]),ALL('MedChecks'[URL])), CALCULATE(SUM('MedChecks'[Med_Count])))

[BBIUser]

@Ashish_Mathur Thanks for your response.

No.

I tried the DAX you provided in the visual filter, it did not work, Here is a screenshot. 

FYI, I still retain the calculated column and measures that I used for the % calculation.

  

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[BBIUser]

@Ashish_Mathur Looks like there was an error in the data submitted. I might need to wait and see if the data changes works  correctly. 

[BBIUser]

Is there any other ideas to recrate the DAX expressions to get the Top 3 results with just 3 fields in a table visual by replacing the existing? I tried few DAX and it is not working.

ColumnCalc = SUM('MedChecks'[Med_Count])

MeasurefrMedCount = CALCULATE(SUM('MedChecks'[Med_Count]))

MedValid% = CALCULATE(DIVIDE('MedChecks'[MeasurefrMedCount], VALUES('MedChecks'[ColumnCalc])))