count distinct based on two date (using OR)

[Anonymous]

Hello, 

I am trying to count the total number of clients within a date range. the trouble is, I would like to count based on two dates. the structure is a project tables and interaction (casenotes) tables. I'd like to do a distinctcount of client record ID based on those that either had an interaction, or a project start date within a filtered range.

My tables look like this - note I put a dates table in there because I thought it might be part of the solution. 

So I am looking for a formula that does a distinct count of clients record id (project table) and I want to use a date range filter on my visual that filters that count by those clients that either had a project start or a session within the filtered range. 

Hope this makes sense?

Any help would be greatly appreciated!

Thanks 

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

Take a look at my attached sample, I believe it does what you are looking for.  Note that my date table is not hooked to either data table. It has to be this way so that the selected months don't filter the tables we are trying to count.
You can see that each month for Jan, Feb and Mar we have 1 client with a start date in that month then in Apr we have 1 client with a start date in Apr and the other 3 have casenotes in Apr so the count is 4.  After that it is the casenotes that is driving the client count but that is just to prove out the formula.

[Anonymous]

Thanks - this is perfect and the logic you used is really hepful to start thinking about. I think i needed the project id to be linked between the notes and the project tables. However, I didn't understand your comment on the lack of a link between the dates table and anything else. When this date is on my visual, I can use it to filter as required and I can see that that is based on the logic of the measure. I thought that the reason we had to do it this way was to keep the integrity between the project record id relationship?

[lbendlin]

Remove the link between projects and case notes. Activate the link between projects and calendar. mark the calendar table as dates table.

Then, based on the date you can list the number of session and the number of started projects in the same visual

[Anonymous]

Thanks for your reply, this looks like a nice and easy solution, but i feared that i would need the relationship by project id?

[jdbuchanan71]

You will, yes.  Take a look at my sample file to see how I set up the model.