cancel
Showing results for
Did you mean:
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

Thanks in advance for your help and support;

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
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

Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution!

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

Really appreciate your help;

WGAP75

Highlighted
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

Did I answer your question? Mark my post as a solution!

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

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

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 (1,759)