Retrieve single column value based on calculation on another column

[lazzarjovvch74]

Hello,

This is my Power Bi table where the column "Calls" is calculated column ( a kind of counter). I want to get Local Start Hour when the highest number of Calls were started for a selected name.

I've been trying to summarize table by Name and Local Start Hours in order to calculate total # of calls started on every hour, but I couldn't get what I wanted.

For example: If I select "John Smith" and an appropriate date range the result can be "10:00 AM" which means that John Smith made the most calls where Local Start Hour is 10:00.

Table example:

Name	Date	Local Time	Local Start Hour	Calls
John Smith	12/2/2019	10:35 AM	10:00:00	1
John Smith	12/2/2019	10:36 AM	10:00:00	1
John Smith	12/2/2019	11:14 AM	11:00:00	1
David Johnson	12/2/2019	11:15 AM	11:00:00	1
David Johnson	12/3/2019	11:21 AM	11:00:00	1
David Johnson	12/3/2019	11:28 AM	11:00:00	1
David Johnson	12/3/2019	11:50 AM	11:00:00	1
David Johnson	12/3/2019	12:30 PM	12:00:00	1
David Johnson	12/3/2019	12:36 PM	12:00:00	1

[VasTg]

@lazzarjovvch74 

Please follow the steps.

Step 1: Group by your table as below.

Create a DAX measure as follows.

Measure = CALCULATE(MAX('Table'[Local Start Hour]), FILTER('Table','Table'[Sum of Calls]=MAX('Table'[Sum of Calls])))

If this helps, mark it as a solution.

Kudos are nice too.

[parry2k]

@lazzarjovvch74 Although @Greg_Deckler  has already provided  a solution, sharing another thought on this

Add following measure

Max hour = 
CALCULATE( 
MAX ( HR[Local Start Hour] ),  
TOPN( 1, ALLSELECTED( HR[Local Start Hour] ), [Call], DESC ) 
) 

[lazzarjovvch74]

@parry2k thank you for submitting your solution.

At the end of your code, I couldn't use just "Calls", but I had to use an aggregation function. Just to remind you that "Calls" is calculated column, not measure. But the code below doesn't retrieve the correct result yet.

Max hour = CALCULATE(MAX(Table1[Local Start Hour]), TOPN(1, ALLSELECTED(Table1[Local Start Hour]), SUM(Table1[Calls]), DESC))

[parry2k]

@lazzarjovvch74 sorry I missed to mention that Call is a measure

Call = SUM ( HR[Calls] )

Max Hour = 
CALCULATE( 
MAX ( HR[Local Start Hour] ),  
TOPN( 1, ALLSELECTED( HR[Local Start Hour] ), [Call], DESC ) 
) 

[lazzarjovvch74]

@parry2k thank you. Your solution works as well!

[Greg_Deckler · Accepted solution]

Perhaps:

Measure 3 = 
    VAR __Name = MAX('Table7'[Name])
    VAR __Table = SUMMARIZE('Table7',[Name],[Local Start Hour],"__Calls",SUM([Calls]))
    VAR __Max = MAXX(FILTER(__Table,[Name] = __Name),[__Calls])
RETURN
    MINX(FILTER(__Table,[Name] = __Name && [__Calls] = __Max),[Local Start Hour])

Page 5, Table 7