Countrows Measure

[-Bri]

I first created 2 measures, one for "Existing" and one for "New", based off column Patient Nbr and column Date (Year_Month_NM). I then created a measure to count "Existing" and it works fine. But when I create the measure for "New", it's blank. Why is this? What am I doing wrong?

  

  

  

 

I started with one measure that identified Existing and New together, but I split them thinking maybe that was the problem. But it doesn't seem to be.

What I ultimately am needing is to track the number of existing, new, and eventually, discontinued patients each month (which will be another nightmare in itself). But first things first, I need to figure out why I can't count "New".

I hope I've included enough data. Any help would be appreciated! Thanks!!

[-Bri]

[Ritaf1983]

Hi @-Bri 

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[-Bri]

Snip-it of my data tables/columns. I use the DATE from the DIM_STANDARD_CALENDAR table (year_month_nm). The Dispenses table includes all patient dispenses and I only need MAP patient dispenses information. I parsed out the appropriate patients in column MAP CODES and I also made it a whole number column. PATIENT_NBR and PICKUP_DATE  are columns in the DISPENSES table. The measures I crated are listed above.

I’m wanting to track if the patients are Existing, New, or Discontinued. My logic is something like this:

• If the patient number shows up the previous month or months (like skipping a month or two), the patient would be Existing
• If the patient number does not show up the previous month or months, the patient would be New
• If the patient number from the previous months does not show up on the current month, the patient would be discontinued (this one I haven’t figured out yet)

What I ultimately am trying to achieve is shown at the bottom of the page.

But I’m stuck on counting NEW patients. I was able to create a measure to count Existing patients, but that same type of measure doesn’t work for counting NEW patients for some reason. I was able to create a measure (with the help from Microsoft Fabric Community) to determine if the patient was Existing or New, I just can’t count the NEW. I feel like I’m going around in circles, so I hope this makes sense. Thanks for your help!

Dummy data:

DATE	PATIENT NUMBER	EXISTING PTS	NEW PTS	DISCONTINUED PTS
2024-01	111		NEW
2024-01	112	EXISTING
2024-01	113		NEW
2024-01	114		NEW
2024-01	115		NEW
2024-01	116			DISCONTINED
2024-01	117		NEW
2024-02	111	EXISTING
2024-02	112			DISCONTINUED
2024-02	113			DISCONTINUED
2024-02	114			DISCONTINUED
2024-02	115			DISCONTINUED
2024-02	116	EXISTING
2024-02	117			DISCONTINUED
2024-02	118		NEW
2024-02	119		NEW
2024-02	120		NEW
2024-02	121		NEW
2024-03	111	EXISTING
2024-03	116			DISCONTINUED
2024-03	118			DISCONTINUED
2024-03	119			DISCONTINUED
2024-03	120	EXISTING
2024-03	121	EXISTING
2024-03	122		NEW
2024-03	123		NEW
2024-03	124		NEW
2024-04	111	EXISTING
2024-04	116	EXISTING
2024-04	120	EXISTING
2024-04	121			DISCONTINUED
2024-04	122			DISCONTINUED
2024-04	123			DISCONTINUED
2024-04	124			DISCONTINUED
2024-04	125		NEW
2024-04	126		NEW
2024-04	127		NEW
2024-05	111			DISCONTINUED
2024-05	116			DISCONTINUED
2024-05	120	EXISTING
2024-05	125			DISCONTINUED
2024-05	126			DISCONTINUED
2024-05	127			DISCONTINUED
2024-05	128		NEW
2024-05	129		NEW
2024-05	130		NEW
2024-05	131		NEW
2024-05	132		NEW
2024-05	133		NEW
2024-06	120	EXISTING
2024-06	128	EXISTING
2024-06	129	EXISTING
2024-06	130			DISCONTINUED
2024-06	131			DISCONTINUED
2024-06	132			DISCONTINUED
2024-06	133			DISCONTINUED
2024-06	134		NEW
2024-06	135		NEW
2024-06	136		NEW
2024-06	137		NEW
		13	25	22

What I’m trying to achieve:

DATE	EXISTING PTS	NEW PTS	DISCONTINUED PTS
2024-01	1	5	1
2024-02	2	4	5
2024-03	3	3	3
2024-04	3	3	4
2024-05	1	6	5
2024-06	3	4	4
	13	25	22