Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Sorry, english is not my first language.
I have a phone number dataset like so.
1 [from] [start time] [end time] [date]
I'm searching for a formula to create the following logic.
Call 1 [xxx] [10 AM] [10:15AM]
Call 2 [yyy] [10:10AM] [10:30AM]
Call 3 [bbb] [11:30 AM] [12:00AM}
Desired output
Time Frame Count of sustained calls
[10:AM] 1
[10:15AM] 2
[10:30 AM] 1
[10:45AM]
[11:00AM]
[11:15AM]
[11:30 AM] 1
[11:45AM] 1
[12:00AM] 1
I'm very new at this and would appriciate the help
Solved! Go to Solution.
Never mind, I found the solution. If I have the tables linked with an active relationship, you can use crossfilter like so:
Probably this:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364
Maybe this:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085#M306
Thank you for the reply.
Unfortunatly, I was not able to achive the desired results.
I attempted to run the following mesure:
Hi @Anonymous ,
I try creating a calculated table to implement your requirement. Maybe it can help you.
Table 2 = GENERATESERIES(TIME(0,0,0),TIME(23,59,59),TIME(0,15,0))
Last Time =
CALCULATE (
MAX ( 'Table 2'[Value] ),
FILTER ( 'Table 2', 'Table 2'[Value] < EARLIER ( 'Table 2'[Value] ) )
)
Count number =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[Start date] <= 'Table 2'[Value]
&& 'Table'[End date] > 'Table 2'[Last time]
)
)
If you want to use measures, you could try like this:
Measure =
CALCULATE (
COUNT ( 'Table'[Call] ),
FILTER (
'Table',
'Table'[Start date] <= MAX ( 'Table 2'[Value] )
&& 'Table'[End date] > MAX ( 'Table 2'[Last Time] )
)
)
For more details, please download the attachment.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Your solution did not work well for my case, however it did allow me to distinguish the differences between your example and mine, thank you.
In short, I have an active relation between [Value] & [Start Date] that messes up with the mesure. Is there any way to keep that relation active, as it is used to graph other events in time other then the calls on the same graph.
Thank you in advance for your awsome work @!
Never mind, I found the solution. If I have the tables linked with an active relationship, you can use crossfilter like so:
After a few hours of trying, I found this to work best with detached time tables:
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
89 | |
87 | |
35 | |
35 |
User | Count |
---|---|
152 | |
99 | |
84 | |
63 | |
54 |