Forum Discussion

cepiolot's avatar
cepiolot
Frequent Visitor
9 years ago

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 r...
Need Help
alanhodgson's avatar
alanhodgson
Icon for Solution Supplier rankSolution Supplier
9 years ago

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's avatar
    cepiolot
    Frequent Visitor
    9 years ago

    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.