cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dutifuldax
Helper III
Helper III

count distinct based on two date (using OR)

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. 

clients by project or casenote.PNG

 

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 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

Hello @dutifuldax 

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.

2020-06-06_6-40-30.png

View solution in original post

5 REPLIES 5
jdbuchanan71
Super User
Super User

Hello @dutifuldax 

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.

2020-06-06_6-40-30.png

View solution in original post

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
Super User
Super User

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

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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!