Forum Discussion

Daffness's avatar
Daffness
New Member
2 years ago
Solved

Find the top 1 for rows

Hi everyone,

 

I'm struggling with the following and was hoping someone would be able to help.

 

I have the following example table of my dataset:

ClientEmployeeInteractions
Client 1Lisa7
Client 1John20
Client 2Lisa9
Client 2John14
Client 2Sean33

 

and I need a Dax query that will return a table that will show the top 1 employee per client based on maximum interactions. For example in the table below:

ClientEmployeeInteractions
Client 1John20
Client 2Sean33
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    Try the following :

    Top Employee by Client = 
SUMMARIZE(
    Emp,  
    Emp[Client],
    "Top Employee", CALCULATE(MAXX(
        FILTER(
            Emp,
            Emp[Interactions] = MAX(Emp[Interactions])
        ),
        Emp[Employee]
    )),
    "Max Interactions", MAX(Emp[Interactions])
)

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Daffness ,

    Here some steps that I want to share, you can check them if they suitable for your requirement.

    Here is my test data:

    Create two measures

    TopEmployeeByInteractions = 
VAR TopInteractionPerClient = 
    CALCULATETABLE(
        TOPN(
            1, 
            SUMMARIZE(
                'Table', 
                'Table'[Client], 
                'Table'[Employee], 
                "TotalInteractions", SUM('Table'[Interactions])
            ), 
            [TotalInteractions], 
            DESC
        ),
        ALLEXCEPT('Table', 'Table'[Client])
    )
RETURN
    MAXX(TopInteractionPerClient, [Employee])
    TopInterations = 
VAR _t = 
SUMMARIZE('Table',
        'Table'[Client],
        "Top",[TopEmployeeByInteractions],
        "Interations",MAX('Table'[Interactions])
            )
RETURN
MAXX(_t,[Interations])

    Final output

    Best regards,

    Albert He

     

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

3 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    Try the following :

    Top Employee by Client = 
SUMMARIZE(
    Emp,  
    Emp[Client],
    "Top Employee", CALCULATE(MAXX(
        FILTER(
            Emp,
            Emp[Interactions] = MAX(Emp[Interactions])
        ),
        Emp[Employee]
    )),
    "Max Interactions", MAX(Emp[Interactions])
)

     

    • Daffness's avatar
      Daffness
      New Member
      2 years ago

      Hi AmiraBedh,

       

      That worked perfectly! Definitely a solution I'm going to use going forward!.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Daffness ,

    Here some steps that I want to share, you can check them if they suitable for your requirement.

    Here is my test data:

    Create two measures

    TopEmployeeByInteractions = 
VAR TopInteractionPerClient = 
    CALCULATETABLE(
        TOPN(
            1, 
            SUMMARIZE(
                'Table', 
                'Table'[Client], 
                'Table'[Employee], 
                "TotalInteractions", SUM('Table'[Interactions])
            ), 
            [TotalInteractions], 
            DESC
        ),
        ALLEXCEPT('Table', 'Table'[Client])
    )
RETURN
    MAXX(TopInteractionPerClient, [Employee])
    TopInterations = 
VAR _t = 
SUMMARIZE('Table',
        'Table'[Client],
        "Top",[TopEmployeeByInteractions],
        "Interations",MAX('Table'[Interactions])
            )
RETURN
MAXX(_t,[Interations])

    Final output

    Best regards,

    Albert He

     

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