only show the first 15 rows

Hi Anonymous ,

 

Since the "Top N" filter isn't available in your case, you can achieve the desired result by using a combination of ranking with DAX measures and visual-level filters. Here’s a complete solution to dynamically filter your table visual to show only the top 15 items by Qty Sold for a selected day.

First, create a DAX measure to rank the items based on their total Qty Sold:

Rank by Qty Sold = 
RANKX(
    FILTER(
        ALL('Table'[Item Name]),
        CALCULATE(SUM('Table'[Qty Sold])) > 0
    ),
    CALCULATE(SUM('Table'[Qty Sold])),
    ,
    DESC,
    DENSE
)

This measure ranks the items in descending order of Qty Sold, ensuring that only items with sales greater than zero are included.

Next, create a second DAX measure to filter the top 15 items:

Top 15 Filter = 
IF([Rank by Qty Sold] <= 15, 1, 0)

This measure returns 1 if an item is ranked within the top 15 and 0 otherwise.

Apply this measure as a visual-level filter in your table visual:

1. Go to the "Filters on this visual" pane.
2. Drag the Top 15 Filter measure to the filters area.
3. Set the filter condition to "is equal to 1."

This method will ensure that your table visual dynamically shows only the top 15 items by Qty Sold, even if the "Top N" option is not available directly in your filters.

If you have a date slicer applied, the ranking will automatically adjust to show the top 15 items for the selected date. If needed, ensure that your DAX calculations respect any date filter context by wrapping the measure inside a CALCULATE function with date filters.

 

Best regards,