Find Or Filter On Maximum Transaction Number For Each Store

[rbowen]

I have a database table coming into one of my BI Desktop reports that shows the number of transactions our customers have done at our various stores. Each customer can potentially make a purchase (transaction) at multiple stores throughout the course of any given day/month. The table below is an example of two customer accounts. 

What I'm trying to do is filter the table above to show what store each customer did the most transactions with.  So, in the example above, the final result would look like this:

There are several thousand customers and around 58 different stores and what I need is to find the store having the most transactions for each of those numerous customer accounts. The final result could be a measure, calculated column or even a new table (as long as it keeps the 5 columns so I can link the customer account number to other tables in the report). The report will refresh the data daily so the most used store may change for a given customer/customers over time so this has to be a dynamic solution. 

Any guidance would be greatly appreciated.  Thank you. 

[v-weiyan1-msft · Accepted solution]

Hi @rbowen ,

Based on the example and description you provided, Please try code as below to create measure.
My Sample:

Max_Counter = 
CALCULATE (
    MAX ( 'Table'[Transaction Counter] ),
    ALLEXCEPT ( 'Table', 'Table'[Customer Number] )
)

Correspond_StoreNumber = 
MAXX (
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Customer Number] ),
        'Table'[Transaction Counter] = [Max_Counter]
    ),
    'Table'[Store Number]
)

Correspond_StoreName = 
MAXX (
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Customer Number] ),
        'Table'[Transaction Counter] = [Max_Counter]
    ),
    'Table'[Store Name]
)

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rbowen]

Apologies for the lengthy delay in responding. While the measures worked, I'm finding that I need to create a separate or calculated table to get a 1 to many relationship to get the visuals working correctly. Is there a way of doing this so that I get the most used stores for each customer in a table?