TopN filter returning more than requested

[Anonymous]

I have a table where I am requesting the last 10 items completed by date.  In my dataset there can be more than one item completed on a single day.  This is causing my TopN filter to return the incorrect number of values.   In this instance I have requested 10 but am getting 14 due to 4 items being completed on the same day as another.

14 ResponsesFilter

[v-lid-msft]

Hi @Anonymous ,

TopN() returns top N rows based on the Response Date. However if the Response Date has same values, those rows will be retrieved. If you do not want to get the same date, we can use the rankx function, create a table using the following DAX.

FilterTable = 
VAR t =
    ADDCOLUMNS (
        'Table',
        "r", RANKX ( ALL ( 'Table' ), 'Table'[Response Date],, ASC )
    )
VAR result =
    FILTER ( t, [r] <= 10 )
RETURN
    SELECTCOLUMNS (
        result,
        "Name", [Name],
        "Response Date", [Response Date],
        "Cycle Time", [Cycle Time]
    )

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[d_gosbell · Accepted solution]

This is because you are filtering the response date column. So you are asking Power BI to give you a list of 10 dates and then it filteres the table by that.

If you want just 10 rows of data you would need to apply your filter to some unique column like a responseID that has a different value in each row. You would still use the same condition (top 10 by Latest Response Date) just change the column that you are filtering.

[RP7]

 

Hi Guys,

The above graph has been filtered to TOP 5 by DPV (line). However, its showing top 6 because the last 2 categories (Fixing Missing, Incomplete) have the same DPV value (3.0). Is there any way I can only show Top 5 please? I don't have any columns that have unique values.

Thanks,

Ram