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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors