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

[ch4rch]

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?

[amitchandak · Accepted solution]

@ch4rch , not very clear try like

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

This will count distinct name and date

[harshnathani]

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

[ch4rch]

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.

[amitchandak]

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

[ch4rch]

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.

[harshnathani]

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)

[ch4rch]

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? 

[harshnathani]

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-La...

Regards,

Harsh Nathani