Forum Discussion
How to filter distinct count across multiple dates
The reason for the count not showing what you expected it to show is possibly because of a problem with the relationship between your two tables, as you are running a distinct count on the values in one table based on the dates in the other. Does this make sense?
Does your Appointments table have a column for Company Name? And does this column have an active relationship with the equivalent column in your Companies table?
Also, how are you wanting to visualise the data? Card, Matrix...?
Bullius,
Many thanks for your response; having reviewed the existing (active) relationships I made a change to the "cross filter direction" from "single" to "both" and this resolved the issue.
Thank you again for your time and efforts in pointing me in the right direction.
Hi diederd,
I'm not sure exactly what you are looking for. Please define the difference between what you want:
"distinct count for all meetings taking place within a particular date range."
And what you are able to produce:
"count for all meetings (between a date range) which includes multiple meetings per company."
Obviously, the latter includes "...multiple meetings per company". Is this what you want to avoid?
Bullius,
Thank you for your response; apologies for not being clear on my request. My end goal is as follows:
- Distinct company count whereby a meeting was had within the date range
- Thus if based on the following scenario
Company A has 3 meetings take place within the period
Company B has 1 meeting take plcae within the period
Company C has no meetings take place during the period
Thus total distinct company count for the period would be 2.
Hopefully that makes more sense?
Understood.
Your formula is fine, except for the column you are applying the distinct count to. That needs to contain only the company; not the meeting. From your example appointments table, it appears to contain both?
e.g.
Company Meeting A 1 A 2 B 1 B 2 C 1 C 2 CALCULATE ( DISTINCTCOUNT ( Table[Company] ) ...
Bullius
Thank you for your help; although I'm afraid that doesn't provide me with the result I'm looking for. Doing the DISTINTCOUNT on Company table returns results in excess of 650+, whereas I'm only expecting in the region of 270. Not sure as to where I'm going wrong.
Are you able to give an example of what your data actually looks like?