How to filter distinct count across multiple dates

[diederd]

Hello Power BI community

I'm needing some assistance in being able to determine a distinct count (on a company table) whereby in a related table I have a number of meetings associated with each company.

Company Table

----------------

Company A

Company B

Company C

Appointments Table

----------------------

Company A meeting 1

Company A meeting 2

Company C meeting 1

Company A meeting 3

Company B meeting 1

The idea is that I'm looking to provide a distinct count for all meetings taking place within a particular date range. I have been struggling to make sense of it, however I'm able to generate a count for all meetings (between a date range) which includes multiple meetings per company. My measure currently:

CALCULATE(DISTINCTCOUNT(appointments[activityid]), appointments[scheduledstart] >= DATE(2016,07,01) && appointments[scheduledstart] <= DATE(2016,09,30) )

Any assistance on the request would be much appreciated.

Thanks in advance.

[bullius · Accepted solution]

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]

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?

[diederd]

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?

[bullius]

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] ) ...

[diederd]

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.

[bullius]

Are you able to give an example of what your data actually looks like?

[diederd]

Bullius,

Below is what I'm able to generate which includes the correct date critera, however pulls back all the meetings for that particular company. In this case, Company 1 had 4 meetings take place within the date period - however I would want the counter to be one for this company.

[bullius · Accepted solution]

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

[diederd · Accepted solution]

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.

[bullius]

Glad to help!