Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
@ch4rch , not very clear try like
COUNTROWS(SUMMARIZE('Table','Table'[Name],'Table'[Date]))
This will count distinct name and date
@ch4rch , not very clear try like
COUNTROWS(SUMMARIZE('Table','Table'[Name],'Table'[Date]))
This will count distinct name and date
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
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.
@ch4rch , I hope the comment was not for the formula I shared. Seems like that did not work.
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.
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)
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?
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |