cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

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

WGAP75

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

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

Hi @WGAP75 .

Try the following measure:

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

Regards,

MFelix

Proud to be a Datanaut!

Super User

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

Hi @WGAP75 ,

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

Proud to be a Datanaut!

6 REPLIES 6
Super User

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

Hi @WGAP75 .

Try the following measure:

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

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

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

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

Super User

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

Hi @WGAP75 ,

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

Proud to be a Datanaut!

Frequent Visitor

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

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

WGAP75

Super User

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

Hi @WGAP75 ,

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

Proud to be a Datanaut!

Frequent Visitor

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

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

Thanks a lot for your Help, really appreciate it;

WGAP75

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,999)