Calculate the Maximum number of matches for Distinct values in a column

[Anonymous]

Dear Community;

I need to solve an smaall issue and count on you!!!!! 

I've a set of data like this:

Date	Key
09/01/19	D1:ACA-P1
09/02/19	D1:ACA-P2
09/03/19	D1:ACA-P3
09/04/19	D1:ACA-P4
09/05/19	D1:ACA-P5
09/02/19	D1:ACA-P2
09/03/19	D1:ACA-P3
09/01/19	D1:ACA-P1
09/02/19	D1:ACA-P2
09/03/19	D1:ACA-P3
09/04/19	D1:ACA-P4
09/01/19	D1:ACA-P1
09/02/19	D1:ACA-P2
09/03/19	D1:ACA-P3
09/04/19	D1:ACA-P4
09/05/19	D1:ACA-P5
09/01/19	D1:ACA-P1
09/02/19	D1:ACA-P2
09/03/19	D1:ACA-P3

I need to get the maximum value for the "Key"

Key	Count
D1:ACA-P1	4
D1:ACA-P2	5
D1:ACA-P3	5
D1:ACA-P4	3
D1:ACA-P5	2

Final Value Needed, filters are required on Date: 

MAX(Count) -->  5

Thanks in advance for your help and support;

WGAP75

[MFelix · Accepted solution]

Hi @Anonymous .

Try the following measure:

Count max =
MAXX (
    SUMMARIZE (
        ALLSELECTED ( DAta_Key[Key] );
        DAta_Key[Key];
        "@Count"; COUNT ( DAta_Key[Key] )
    );
    MAX ( [@Count] )
)

Regards,

MFelix

[MFelix · Accepted solution]

Hi @Anonymous .

Try the following measure:

Count max =
MAXX (
    SUMMARIZE (
        ALLSELECTED ( DAta_Key[Key] );
        DAta_Key[Key];
        "@Count"; COUNT ( DAta_Key[Key] )
    );
    MAX ( [@Count] )
)

Regards,

MFelix

[Anonymous]

@MFelix;

Really appreciate yout support, your solution was really helpful, however, once I had additional progress building my Data Model, I found some additional challenges, hope you can help me again.

This time I need to achieve 2 things:

1) Get the Maximum Value for "ReqCount"  the Table below based on DISTINCT "NetKey2"

2) Be able to make Aggregations (SUM) from the values gotten on 1

Herein the "TableTest" that I got from SUMMARIZED Function:

ReqCount	Asset Requirement	NetReqKey	NetKey2
1	VXFM-C	1915:VXFM-C:1/10/2019	1915:VXFM-C
1	VXFM-C	1915:VXFM-C:1/11/2019	1915:VXFM-C
1	VXFM-C	1915:VXFM-C:2/10/2019	1915:VXFM-C
1	VXFM-C	1915:VXFM-C:2/11/2019	1915:VXFM-C
1	VXFM-C	1915:VXFM-C:3/10/2019	1915:VXFM-C
2	VXFM-C	1915:VXFM-C:3/11/2019	1915:VXFM-C
1	VXFM-C	1915:VXFM-C:4/10/2019	1915:VXFM-C
1	VXFM-C	1915:VXFM-C:4/11/2019	1915:VXFM-C
2	VXFM-C	1915:VXFM-C:5/10/2019	1915:VXFM-C
2	VXFM-C	1915:VXFM-C:5/11/2019	1915:VXFM-C
1	VXFM-C	1915:VXFM-C:7/10/2019	1915:VXFM-C
1	VXFM-C	1915:VXFM-C:7/11/2019	1915:VXFM-C
2	VXFM-C	1915:VXFM-C:8/10/2019	1915:VXFM-C
2	VXFM-C	1915:VXFM-C:8/11/2019	1915:VXFM-C
1	VXFM-C	1915:VXFM-C:9/10/2019	1915:VXFM-C
1	VXFM-C	1915:VXFM-C:9/11/2019	1915:VXFM-C
1	VXFM-CC	1915:VXFM-CC:7/10/2019	1915:VXFM-CC
1	VXFM-CC	1915:VXFM-CC:7/11/2019	1915:VXFM-CC
1	VXFM-E	1915:VXFM-E:4/10/2019	1915:VXFM-E
1	VXFM-E	1915:VXFM-E:4/11/2019	1915:VXFM-E
1	VXFM-E	1915:VXFM-E:5/10/2019	1915:VXFM-E
1	VXFM-E	1915:VXFM-E:5/11/2019	1915:VXFM-E
1	VXFM-E	1915:VXFM-E:6/10/2019	1915:VXFM-E
1	VXFM-E	1915:VXFM-E:6/11/2019	1915:VXFM-E
2	VXFM-E	1915:VXFM-E:7/10/2019	1915:VXFM-E
2	VXFM-E	1915:VXFM-E:7/11/2019	1915:VXFM-E

Thanks & Regards;

WGAP75

[MFelix]

Hi @Anonymous ,

Believe that you are looking for the following codes:

Count max = 
MAXX (
    SUMMARIZE (
        ALLSELECTED ( Req[ReqCount];Req[NetKey2] );
        Req[NetKey2];
        "@Max"; max ( Req[ReqCount] )
    );
    MAX ( [@Max] )
)

Sum Count= SUMX(ALLSELECTED(Req[NetKey2]);[Count max])

The seconde one counts all the max values of the NetKey2 in your case there are 3 values equal to 2 so is 6, is this correct?

Regards,

MFelix

[Anonymous]

@MFelix ;

Thans AGAIN for your help !!!!!

Point 1 is OK 

I wasn't clear enough in my point 2, What I need to calculate is the SUM of the DISTINCT MAX Values:

1915:VXFM-C --> MAX = 2

1915:VXFM-CC --> MAX = 1

1915:VXFM-E --> MAX = 2

Required Result = 5

Really appreciate your help;

WGAP75

[MFelix · Accepted solution]

Hi @Anonymous ,

Replace both formulas for the one below should work for both purposes:

Count max = 
SUMX(SUMMARIZE(Req;Req[NetKey2];"@Max";MAX(Req[ReqCount]));[@Max])

Regards,

MFelix

[Anonymous]

@MFelix ;

You're the best Man !!!!!!!

Thanks a lot for your Help, really appreciate it;

WGAP75