Forum Discussion
Retrieve single column value based on calculation on another column
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 |
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
6 Replies
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
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.
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 ) )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))
