topic Re: calculate the number of working agents [Name] by number of dates [Date] in DAX Commands and Tips

calculate the number of working agents [Name] by number of dates [Date]

ch4rch — Mon, 17 Aug 2020 11:20:10 GMT

I have understood that multiply doesn't exist in DAX in the same way that divide does. I am trying to calculate the number of working agents [Name] by number of dates [Date] But I need the Distinct count of the name as each name can appear 20 times on a date. Essentially the sum would be Multiply DISTINCTCOUNT [NAME], SUM [Date] but that obviously does not work. Any ideas?

Re: calculate the number of working agents [Name] by number of dates [Date]

Anonymous — Mon, 17 Aug 2020 11:24:51 GMT

Hi ch4rch ,

You can use * or Product in DAX.

Not sure what is the usecase.

Total = DISTINCTCOUNT(Table [Agent Name]) * COUNT(Table[Dates])

Regards,

Harsh Nathani

Re: calculate the number of working agents [Name] by number of dates [Date]

amitchandak — Mon, 17 Aug 2020 11:36:39 GMT

ch4rch , not very clear try like

COUNTROWS(SUMMARIZE('Table','Table'[Name],'Table'[Date]))

This will count distinct name and date

Re: calculate the number of working agents [Name] by number of dates [Date]

ch4rch — Mon, 17 Aug 2020 12:28:19 GMT

Thanks, this works in that it gives me the number for all dates but if I filter the dates it does not seem to change.

Re: calculate the number of working agents [Name] by number of dates [Date]

Anonymous — Mon, 17 Aug 2020 12:42:43 GMT

Hi ch4rch ,

Please share sample data and expected output?

not very clear.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: calculate the number of working agents [Name] by number of dates [Date]

ch4rch — Mon, 17 Aug 2020 12:49:00 GMT

Hi,

I cannot really share data as it contains company information.

What I have is a list of interactions in a call centre.

Columns are:

Date/Time

Date only

Agent Name

Team

Media

Direction

Talk Time

Wait Time

And a few others that are not relevent.

So for a given data an Agents name may appear in 20 rows however I am trying to understand how many unique agents worked on a given day multiplied by the number of dates in the filter.

Using a DISTINCTCOUNT of Agent Names I get the number of Unique Agents that worked in that period but I am trying to work out how many agent days I had in a week.

Does that make sense?

Re: calculate the number of working agents [Name] by number of dates [Date]

amitchandak — Mon, 17 Aug 2020 12:54:10 GMT

ch4rch , I hope the comment was not for the formula I shared. Seems like that did not work.

Re: calculate the number of working agents [Name] by number of dates [Date]

ch4rch — Mon, 17 Aug 2020 13:05:47 GMT

Hi,

I initially dismissed your solution as to me (who clearly does not know what he is doing) it didn't look like it answered my question. However given your follow up comment I tried it and I think you have solved my issue.

I apologise, thank you so much. never again will I be so dismissive.

Re: calculate the number of working agents [Name] by number of dates [Date]

Anonymous — Mon, 17 Aug 2020 13:08:14 GMT

Hi ch4rch ,

Incase you need weeks, see if this helps you.

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Regards,

Harsh Nathani