cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cepiolot
Frequent Visitor

Top N not returning the top amounts

I'm using the top N filter on table attribute start_datetime to try and filter a multi-card to only display the date with the most users to ever use the system. The amount of users on the system is represented with the attribute total_clients. However, when I set the filter for the multi-card, instead of showing the date with the most amount of users, it displays the date with the second highest amount of users, as shown in this screenshot:

topn2.PNGtopn1.PNG

And when I say filter the top 3:

topn3.PNGtopn4.PNG

Then the top result shows up. It doesn't only act this way for the top 1, it also gets other elements out of their proper order as well if you select top 5, etcetera. Am I doing something wrong? How do I go about fixing this? Thanks in advance.

2 REPLIES 2
alanhodgson
Solution Supplier
Solution Supplier

Hey @cepiolot,

 

I can't seem to replicate your problem. It looks like PBI is having trouble with your date format. Is that dd/MM/yy one of the Formats available in the "Format" drop down on the Modeling tab?

 

UPDATE:

The only way I was to get it to show the incorrect order was to format the date column with the function:

date2 = Format([Date], "dd/MM/yyyy")

See below:

 

BII1.PNG

 

Hope this helps,

 

Alan

Hi Alan,

 

The format in my example was the (G) format (first option under Date/Time formats). However, I tried the same example with a date in plain text format and I get the same results. It was my thinking that since I'm filtering by the top total_clients, the date format shouldn't matter. When I tried the example with the just the date as dd-MM-yyyy (and switch the total_clients summary to "Sum"), top 1 actually shows the top 1, top 2 the actual top 2, and so on. This leads me to believe that Power BI is having issues with having 24 times the amount of rows to filter through when the hours are included. Have you been able to find any other causes for this issue?

 

Much appreciated,

Darcy

 

EDIT: Just to note, I am using Direct Query mode, so I am limited in terms of what functions I can use. For example, I can't use the Format() function for calculated columns.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.