Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
BBIUser
Helper IV
Helper IV

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

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)

1.png  

 

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

2.png

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!

 

1 ACCEPTED SOLUTION

Hi,

Does this work

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
BBIUser
Helper IV
Helper IV

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.

Hi,

Does this work

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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.

3.png  4.png

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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. 

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])))

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.