Top N not returning the top amounts

[cepiolot]

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:

And when I say filter the top 3:

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.

[alanhodgson]

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:

Hope this helps,

Alan

[cepiolot]

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.