I am using a DAX function as below but this is not retuning correct value as even though an agent is in 2/3 chats at the same time (before the end of earlier chat), the measure only shows as 1. How to fix?
You can create a calculated column with this DAX
Count of Concurrent Chat =
COUNTROWS (
FILTER (
'Table',
(
'Table'[Chat Initiated On] < EARLIER ( 'Table'[Chat Initiated On] )
&& 'Table'[Chat Terminated On] > EARLIER ( 'Table'[Chat Initiated On] )
)
|| (
EARLIER ( 'Table'[Chat Terminated On] ) > 'Table'[Chat Initiated On]
&& 'Table'[Chat Initiated On] > EARLIER ( 'Table'[Chat Initiated On] )
)
)
) + 1
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hello,
I am afraid it doesnt work, can you please check once and guide.
Also, please bear in mind that the objective is, we find that on any particular interval, was the agent was (Assigned agent) in 1, 2 or 3 chat as a concurrency
Can you please provide some dummy data to show the table you have? Is [Assigned Agent] column located in the same table as the [Chat initiated on] & [Terminated on] columns? In addition, please provide the expected output based on the dummy data as well as the logic to get there.
As of now I guess that you want to use a slicer to switch the Assigned Agent and show the count of concurrent chats for every chat period of that person. Assume all data is from the same table, I add a new column to that table with below code. Then in the table visual, select Sum instead of Count for the [Count of Concurrent Chat] column.
Count of Concurrent Chat =
COUNTROWS (
FILTER (
'Table',
'Table'[Assigned Agent] = EARLIER ( 'Table'[Assigned Agent] )
&& (
(
'Table'[Chat Initiated On] < EARLIER ( 'Table'[Chat Initiated On] )
&& 'Table'[Chat Terminated On] > EARLIER ( 'Table'[Chat Initiated On] )
)
|| (
EARLIER ( 'Table'[Chat Terminated On] ) > 'Table'[Chat Initiated On]
&& 'Table'[Chat Initiated On] > EARLIER ( 'Table'[Chat Initiated On] )
)
)
)
) + 1
Download the attached pbix to see if it can meet your need. If it's still not what you want, I may have misunderstood your requirement. Please provide dummy data I required in the first paragraph above to help me understand your objective clearly.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Thanks, the solution looks better now but still not completely fixed as if an agent has taken a chat late at night and it extends to the following day, the current code takes it as earlier and gives wrong value.. Can you check , attached is a screenshot.
There is no way I can actually add the PBIX file that I have so that you could see, but if you could let me know a way, I am happy to share..
For now attached image
Thank you for the sample data. I have applied it to the report. Let me know where you think the concurrent count is wrong. To me I think it is correct.
Best Regards,
Jing
Dear Jing,
Firstly, I really appreciate your help but it seems I am just getting crazy with this one..
I am using the same code as is with update on the table as mine is named as 'data' but the results I guess is wrong, attach screenshot where you can see it comes as 22,23 etc
I am doing everything exactly as it's in your PBIX but I cant get through. So I searched again..
I realised that the Agent connected on and Terminated On in my table is in a text format while in yours is date format, do you think - that could be the issue? If yes, how do I fix? As When I try to duplicate the tables and convert to table its gives error "Cannot parse...."
Your help will be appreciated
Yes, it is probably the cause. Both the Agent connected on and Terminated On columns should be in Datetime data type. If it's in Text data type, the comparison with ">" and "<" operators will not work correctly to compare datetime values.
You can change the data type at two places, one is in Power BI Desktop as below.
The other is in Power Query Editor as below. Then click Close&Apply or Apply to apply this change to Power BI Desktop.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Yeah, I am aware of these two but as already mentioned, it wont allow me change the format from text to date/time and gives error. see attached
The sad part is, the database from where I am getting this data is coming in text format only.
Any other trick or solution you know, that could fix this?
Same thing, cannot parse..see attached
Reason is that this dataset is completely crazy, there is a "--" value in Agent terminated on date and that's why it doesnt allow to parse. Even I tried to due it DAX using datevalue..
Anything you could suggest?
Oh I see. You need to clean data before changing the data type. What does "--" represent in the data table? If it represents blank datetime on that row, you can replace it with empty in the column. After changing its data type to datetime, it will display null without any error. Note that null datetime is a very small datetime in Power BI. If your "--" has other meanings, you need to replace it with another valid acceptable datetime value.
If "--" is meaningless in your data and you want to get rid of rows with "--", you can filter out rows that have "--".
After cleaning the data, you can change the data type correctly.
Jing
Helpful resources
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
