Forum Discussion

Atanas_Atanasov's avatar
Atanas_Atanasov
Icon for Helper II rankHelper II
3 years ago
Solved

Counting People Based On Maximum Result

Dear All, I am really struggling with one calculation: Got the table from screenshot 1 where I have number of cases breakdown by person. In the rows I have a name and in the columns I have the BU: ...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Atanas_Atanasov ,

     

    Here I create a sample to have a test.

    Measure:

    Engineer_Count = 
VAR _STEP1 =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[Engineer Email],
        'Table'[BU Name],
        "Count", COUNT ( 'Table'[Engineer Email] )
    )
VAR _STEP2 =
    FILTER (
        _STEP1,
        [Count]
            = MAXX (
                FILTER ( _STEP1, [Engineer Email] = EARLIER ( [Engineer Email] ) ),
                [Count]
            )
    )
RETURN
    COUNTX (
        FILTER ( _STEP2, [BU Name] = MAX ( 'DimBU Name'[BU Name] ) ),
        [Engineer Email]
    ) + 0

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Atanas_Atanasov ,

 

Here I create a sample to have a test.

Measure:

Engineer_Count = 
VAR _STEP1 =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[Engineer Email],
        'Table'[BU Name],
        "Count", COUNT ( 'Table'[Engineer Email] )
    )
VAR _STEP2 =
    FILTER (
        _STEP1,
        [Count]
            = MAXX (
                FILTER ( _STEP1, [Engineer Email] = EARLIER ( [Engineer Email] ) ),
                [Count]
            )
    )
RETURN
    COUNTX (
        FILTER ( _STEP2, [BU Name] = MAX ( 'DimBU Name'[BU Name] ) ),
        [Engineer Email]
    ) + 0

Result is as below.

 

Best Regards,
Rico Zhou

 

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

 

 

 

Atanas_Atanasov's avatar
Atanas_Atanasov
Icon for Helper II rankHelper II
3 years ago

Anonymous  - Thank you so much for your suggestion!

I've tried the following code you suggested (please note I am using one - single table:

Eng Count =
VAR Step1 =
SUMMARIZE(
    ALL( 'Closed Cases' ),
    'Closed Cases'[Engineer Email],
    'Closed Cases'[BU (groups)],
    "Count", COUNT( 'Closed Cases'[Engineer Email] ))

VAR Step2 =
FILTER(
    Step1,
    [Count] = MAXX(
        FILTER(
            Step1, 'Closed Cases'[Engineer Email] = EARLIER( 'Closed Cases'[Engineer Email] )), [Count] ))
RETURN
COUNTX(
    FILTER(
        Step2, 'Closed Cases'[BU (groups)] = MAX( 'Closed Cases'[BU (groups)] )), 'Closed Cases'[Engineer Email] ) + 0
but this is the result I get:

All due respect, 

Atanas