cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WGAP75 Frequent Visitor
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!!!!! Smiley Happy

 

I've a set of data like this:

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

 

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

KeyCount
D1:ACA-P14
D1:ACA-P25
D1:ACA-P35
D1:ACA-P43
D1:ACA-P52

 

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
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!




View solution in original post

Super User
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!




View solution in original post

6 REPLIES 6
Super User
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!




View solution in original post

WGAP75 Frequent Visitor
Frequent Visitor

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

@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:

ReqCountAsset RequirementNetReqKeyNetKey2
1VXFM-C1915:VXFM-C:1/10/20191915:VXFM-C
1VXFM-C1915:VXFM-C:1/11/20191915:VXFM-C
1VXFM-C1915:VXFM-C:2/10/20191915:VXFM-C
1VXFM-C1915:VXFM-C:2/11/20191915:VXFM-C
1VXFM-C1915:VXFM-C:3/10/20191915:VXFM-C
2VXFM-C1915:VXFM-C:3/11/20191915:VXFM-C
1VXFM-C1915:VXFM-C:4/10/20191915:VXFM-C
1VXFM-C1915:VXFM-C:4/11/20191915:VXFM-C
2VXFM-C1915:VXFM-C:5/10/20191915:VXFM-C
2VXFM-C1915:VXFM-C:5/11/20191915:VXFM-C
1VXFM-C1915:VXFM-C:7/10/20191915:VXFM-C
1VXFM-C1915:VXFM-C:7/11/20191915:VXFM-C
2VXFM-C1915:VXFM-C:8/10/20191915:VXFM-C
2VXFM-C1915:VXFM-C:8/11/20191915:VXFM-C
1VXFM-C1915:VXFM-C:9/10/20191915:VXFM-C
1VXFM-C1915:VXFM-C:9/11/20191915:VXFM-C
1VXFM-CC1915:VXFM-CC:7/10/20191915:VXFM-CC
1VXFM-CC1915:VXFM-CC:7/11/20191915:VXFM-CC
1VXFM-E1915:VXFM-E:4/10/20191915:VXFM-E
1VXFM-E1915:VXFM-E:4/11/20191915:VXFM-E
1VXFM-E1915:VXFM-E:5/10/20191915:VXFM-E
1VXFM-E1915:VXFM-E:5/11/20191915:VXFM-E
1VXFM-E1915:VXFM-E:6/10/20191915:VXFM-E
1VXFM-E1915:VXFM-E:6/11/20191915:VXFM-E
2VXFM-E1915:VXFM-E:7/10/20191915:VXFM-E
2VXFM-E1915:VXFM-E:7/11/20191915:VXFM-E

 

Thanks & Regards;

WGAP75

Super User
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!




Highlighted
WGAP75 Frequent Visitor
Frequent Visitor

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

@MFelix ;

Thans AGAIN for your help !!!!!

Point 1 is OK Smiley Very Happy

 

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

Super User
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!




View solution in original post

WGAP75 Frequent Visitor
Frequent Visitor

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

@MFelix ;

 

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

Thanks a lot for your Help, really appreciate it;

WGAP75

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

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

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)