Max value filtered and grouped

[Drobb]

Hi, 

I have a moderately specific question that I have been struggling with for some time.  

I have a table called "performance" that has the columns: ID; Sitename; Date; NumberofTransactions.  The 'performance'[Date] column is connected with a many to one relationship to a general 'calendar'[Date] table being used to filter the data. 

I am trying to return the max NumberofTransactions 1 ID has had for each Sitename, filterable by the date. 

So for example, if I had the data set below where ID-Sitename-Date-NumberofTransactions: 

AB-Ancorage-1/1/23-16

AB-Ancorage-1/2/23-18

AB-Ancorage-1/3/23-19

AC-Ancorage-1/2/23-2

AC-Ancorage-1/3/23-29

DA-Dallas-1/2/23-19

DA-Dallas-1/5/23-20

DB-Dallas-1/2/23-17

The Resulting Table would look like this: ID-Site-NumberofTransactions-MaxNumberofTransactionsofSite  when filtered on the dates 1/2/23-1/3/23: 

AB-Ancorage-37-37

AC-Ancorage-31-37

DA-Dallas-19-19

DB-Dallas-17-19

The hardest link for me has been to get it to be filterable by date.   Any support is appreciated.

[Dangar332 · Accepted solution]

hi, @Drobb 

try below measure

max no of side = 
var a =ADDCOLUMNS(ALL(performance[id],performance[sitename]),"sum",CALCULATE(SUM(performance[no of tran]),performance[sitename]=MAX(performance[sitename])))
return
MAXX(FILTER(a,performance[sitename]=MAX(performance[sitename])),[sum])

[Anonymous · Accepted solution]

Hi @Drobb ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

var _table =ADDCOLUMNS(SUMMARIZE(ALL('performance'),[ID],[Sitename]),"sum",CALCULATE(SUM(performance[NumberofTransactions]),performance[sitename]=MAX(performance[sitename])))
return
MAXX(FILTER(_table,[sitename] in VALUES('performance'[Sitename])),[sum])

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Dangar332 · Accepted solution]

hi, @Drobb 

try below measure

max no of side = 
var a =ADDCOLUMNS(ALL(performance[id],performance[sitename]),"sum",CALCULATE(SUM(performance[no of tran]),performance[sitename]=MAX(performance[sitename])))
return
MAXX(FILTER(a,performance[sitename]=MAX(performance[sitename])),[sum])