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




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 Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (6,034)