Forum Discussion

rbowen's avatar
rbowen
Helper III
2 years ago
Solved

Find Or Filter On Maximum Transaction Number For Each Store

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 (tr...
Need Help
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    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.

v-weiyan1-msft's avatar
v-weiyan1-msft
Community Support
2 years ago

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's avatar
    rbowen
    Helper III
    2 years ago

    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?