My Matrix Table Keeps Showing More than X rows despite Measures - How to fix?

Hi - Currently my table (named Overview) is displaying Sites (via distinct site table) and Customers (via distinct customer table) as the Matrix Table's rows and the overall store sales + top 5 customer sales (depending on the time period picked).

Data sheet hyperlink

PBIX file hyperlink

I'm experiencing a problem where:

1. I can't seem to make a Top/Bottom 20 stores slicer (based off sales)

2. Adding Date columns to my Matrix Table rows removes my top 5 Customers row condition and instead shows 6 or more customer rows/exceeds query resources

To illustrate what i'm doing currently - I made the table's sales value by these measures:

Total Sales = SUM( Overview[Sales])

Then i used this measure as the Matrix Table value:

MEASURE-SALES = IF( ISINSCOPE( 'Unique Customers'[Customer] ), [Total Sales], CALCULATE( [Total Sales], REMOVEFILTERS( 'Unique Customers'[Customer] ) ) )

As mentioned before - i also made the table show each store's top 5 customers via this rank:

Customer Rank = VAR BaseTable = ADDCOLUMNS(
    CALCULATETABLE(
        SUMMARIZE(
            Overview, 
            'Unique Customers'[Customer],
            'Unique Stores'[Store]
        ),
        REMOVEFILTERS( 'Unique Customers' )
    ),
    "@val", [Total Sales]
)
VAR Result = RANK(
    SKIP,
    BaseTable,
    ORDERBY( [@val], DESC ),
    PARTITIONBY( 'Unique Stores'[Store] )
)
RETURN Result

then i set this measure as = 1 in the table's filter

Customer is visible = VAR CustomerRank = [Customer Rank]
VAR Result = IF( ( CustomerRank <= 5 && NOT ISBLANK( CustomerRank ) )
    || NOT ISINSCOPE( 'Unique Customers'[Customer] ), 1 )
RETURN Result

My date table is below for reference (capped at current month for YOY measure purposes not mentioned above):

Date = 
ADDCOLUMNS (
    CALENDAR (
        DATE (2023, 1, 1),
        DATE (2025, 4, 30)
    ),
    "Year", YEAR([Date]),
    "Year-Month", FORMAT([Date], "yyyy-MM"),
    "Year-Month sort", EOMONTH([Date], 0)
)

 Would like if there was a test file sample on this if possible as i keep getting stuck/written measures i recieve return an error- the common problems i'm running into for 2. as mentioned before is:
- Query exceeds resource when Year-Month is added

- Rows exceed 5 customers when "Year" column is added / time range on slicer is picked

Hi,

Please try the below and the attached pbix file whether it works.

- remove condition (customer is visible) from filter pane

- add the below measure to the visual.

Customer top5 by store =
VAR BaseTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE ( Overview, 'Unique Customers'[Customer], 'Unique Stores'[Store] ),
            REMOVEFILTERS ( 'Unique Customers'[Customer] ),
            ALLSELECTED ( 'Date'[Year] )
        ),
        "@val", CALCULATE ( [Total Sales], ALLSELECTED ( 'Date'[Year] ) )
    )
VAR Result =
    CALCULATE (
        [Total Sales],
        KEEPFILTERS (
            WINDOW (
                1,
                ABS,
                5,
                ABS,
                BaseTable,
                ORDERBY ( [@val], DESC ),
                ,
                PARTITIONBY ( 'Unique Stores'[Store] )
            )
        )
    )
RETURN
    Result

Hi derekli17001 ,

Thank you for reaching out to Microsoft Fabric Community.

I want to request you that whatever issue you are facing please start conversation here itself, dont make duplicate threads in the community.If you have any issue other than this, you can raise new post in the community instead of raising different posts for the same issue.

Thank you.

Hi derekli17001 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you Jihwan_Kim for the prompt response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Hi derekli17001 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Hi derekli17001 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.