Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Please follow the steps.
Step 1: Group by your table as below.
Create a DAX measure as follows.
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 )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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))
@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 )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |