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

[derekli17001]

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

[antfr99]

Hi @derekli17001 

I noticed this question was open a few days so I decided to give it a try. I tried various approaches with DAX but I had the same issue with “ Query exceeds message…  “.

For this reason I had a look via Power Query if this was possible and came up with the below. The general logic in Power Query for the attached file is  :

Overview Table > Add a key : StoreCustomerKey

Overview Table > Add a key : StoreCustomerPeriodKey

Overview Table > Add a key : StorePeriodKey

Overview Table >Duplicate > Create Top 20 Overall Store Table > Merge to Main Table via Store

Overview Table >Duplicate > Create Bottom 20 Overall Store Table > Merge to Main Table via Store

Overview Table >Duplicate > Create Top 5 Customers Per Store > Merge to Main Table with StoreCustomerKey

Overview Table >Duplicate > Create Top 20 2023 Store Table > Merge to Main Table with StorePeriodKey

Overview Table >Duplicate > Create Bottom 20 2023 Overall Store Table > Merge to Main Table StorePeriodKey

Overview Table >Duplicate > Create Top 5 2023 Customers Per Store > Merge to Main Table with StoreCustomerPeriodKey

Overview Table >Duplicate > Create Top 20 2024 Store Table > Merge to Main Table with StorePeriodKey

Overview Table >Duplicate > Create Bottom 20 2024 Overall Store Table > Merge to Main Table StorePeriodKey

Overview Table >Duplicate > Create Top 5 2024 Customers Per Store > Merge to Main Table with StoreCustomerPeriodKey

Overview Table >Duplicate > Create Top 20 2025 Store Table > Merge to Main Table with StorePeriodKey

Overview Table >Duplicate > Create Bottom 20 2025 Overall Store Table > Merge to Main Table StorePeriodKey

Overview Table >Duplicate > Create Top 5 2025 Customers Per Store > Merge to Main Table with StoreCustomerPeriodKey

Limitations :

-The period can be filtered by Year and not by Month as Month filters would be resource heavy with this mode.  Month is maybe possible ( maybe a python script in power query ) but I am not sure.

-Filters are a little bit too numerous but maybe you can clean that up with well placed slicers

Notes :

-Precise Power Query steps ( with extra custom columns, ranks, etc ) can be viewed if you download the file and connect to excel source.

-Linked only to your Overview table that you provided, but I think you can create relationships with your other tables where required

-Tested it and seems to work, but if you find something that is not correct please let me know . Will have another look.

-Below is a folder with the pbix file which you can link to you excel datasource which you provided in your question ( I worked off your datasource overview which you can link to yourt other tables ).  

Folder with files 

Top 20 Store Overall example :

Top 5 Customers per store

Example Store AS :

Top 5 Customer ( Overall ) Per store AS :

Top 5 Customer ( for year 2025 ) > Store AS :

Hope this helps

Antonio