Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
wherdzik
Helper III
Helper III

Distinct Service Date Count For Each Month

I have a dataset that contains patient visits by date for each month for Calendar Year 2023.  What I am trying to do is to do a distinct count of the patient visit days or the service dates.  The service date column is in the dd/mm/yyyy format. I came from a Tableau world and when I  used to do a distinct count on the dimension it gave me the days count.  I am having  a really hard time  here.  I am getting over 30 days each mont which is not true.  

 

here is the visual

 

wherdzik_0-1698376131522.png

Here are the data fields showing Service Date

wherdzik_1-1698376176851.png

Here is what it should be...I brought the same dataset into excel via power query and for January I got 15 ditinct days:

 

wherdzik_2-1698376238276.png

 

 

I dont get it...how do I get distinct days by month.

 

Can anyone help please...

 

2 REPLIES 2
wherdzik
Helper III
Helper III

Hi Nate, apologizes but I just recently started to use PowerBi more regularly. My last organization was Tableau so had very limited usage of Microsofts tool.  I am super confused.  When I removed the date hierarchy alot of my graphs blew up becuase some of them are using the hierarchy fro month by month trending.  I still need those so I would have to somehow take the service date take the month out of it and tag it again?  Is there a way to duplicate the hierarchy and just get the service date in dd/mm/yyyy format so it knows how to use it accurately?  When playing more with this, I picked a specific person and selected January and added a patient name count...when I do that it accurately captures the service dates.

 

See below:

wherdzik_0-1698413135087.png

But when I take the patient names out of it it gives me every date so I think its 

wherdzik_1-1698413181538.png

Is there a way to do a service date count if there are patients on that service date? Maybe we can do it that way?

I ideally I want to get statistics to give me out of all of my doctors what is the average patients seen and the way to do that is count patients then divide by the service dates.  I want to do this on an enterprise level and then on a doctor level.  Eventually I want to compare if they are above or below the average..

 

Can you help?

 

watkinnc
Super User
Super User

can't be sure, but it looks like you have no date table, but are instead using one giant table with date hierarchies in more than one place. No offense meant, but there is a lot wrong with this data model.

I would remove the hierarchy on Service Date and DOB. Then place Patient and then Month "on rows". Then, your measure is Patient Visits = COUNT(TableName[Service Date]).

 

But really, you need a date table.

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors