Solved: Re: DAX Formula Help > Count one instance of patie...

Anonymous · ‎01-09-2021

Hello - I've been working on this problem for awhile and it seems like there should be an easy solution but alas I've reached the limits of my DAX knowledge and I can't figure out how to construct the right formula for my needs.

Description: I have a data set of patient healthcare claims and am attempting to count emergency room visits. I only want to count one visit per day (SERVICE_START_DATE) per patient (MEM_UNQ_ID) where PLACE_OF_SERVICE = EMERGENCY ROOM - HOSPITAL.

Current Formula:

ED Visits =

IF( CALCULATE(
MAX('Fact tbl_claims'[SERVICE_START_DATE]),
'Fact tbl_claims'[Place of Service] = "EMERGENCY ROOM - HOSPITAL",
FILTER('Fact tbl_claims','Fact tbl_claims'[MEM_UNQ_ID] = EARLIER('Fact tbl_claims'[MEM_UNQ_ID])))
= 'Fact tbl_claims'[SERVICE_START_DATE],
1,
0
)

Sample Data:

MEM_UNQ_ID,CLAIMID,SERVICE_START_DATE,PLACE_OF_SERVICE,ED Visits Formula,Expected ED Visits,
100001,291967,7/8/2016,INPATIENT HOSPITAL,0,,
100001,12385,7/8/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,171115,7/8/2016,INPATIENT HOSPITAL,0,,
100001,195353,7/8/2016,INPATIENT HOSPITAL,0,,
100001,112293,7/9/2016,INPATIENT HOSPITAL,0,,
100001,195083,7/19/2016,AMBULANCE - LAND,0,,
100001,383416,7/19/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,122694,7/20/2016,INPATIENT HOSPITAL,0,,
100001,166662,7/20/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,117812,7/20/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,237430,7/20/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,109742,7/20/2016,OUTPATIENT HOSPITAL,0,,
100001,237320,7/21/2016,INPATIENT HOSPITAL,0,,
100001,154459,7/21/2016,INPATIENT HOSPITAL,0,,
100001,331948,7/22/2016,INPATIENT HOSPITAL,0,,
100001,351690,7/22/2016,INPATIENT HOSPITAL,0,,
100001,41380,7/23/2016,INPATIENT HOSPITAL,0,,
100001,67637,7/25/2016,AMBULANCE - LAND,0,,
100001,154483,7/25/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,302461,7/25/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,227798,7/25/2016,INPATIENT HOSPITAL,0,,
100001,16383,7/25/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,384717,7/26/2016,AMBULANCE - LAND,0,,
100001,165999,7/26/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,183016,7/26/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,302084,7/26/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,205847,7/26/2016,OUTPATIENT HOSPITAL,0,,
100001,371297,7/27/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,185487,7/27/2016,OUTPATIENT HOSPITAL,0,,
100001,162030,7/27/2016,OUTPATIENT HOSPITAL,0,,
100001,142872,7/27/2016,INPATIENT HOSPITAL,0,,
100001,123408,7/27/2016,OUTPATIENT HOSPITAL,0,,
100001,205189,7/28/2016,INPATIENT HOSPITAL,0,,
100001,136717,7/28/2016,OUTPATIENT HOSPITAL,0,,
100001,384209,7/31/2016,INPATIENT HOSPITAL,0,,
100001,416303,8/1/2016,INPATIENT HOSPITAL,0,,
100001,503903,8/1/2016,AMBULANCE - LAND,0,,
100001,654096,8/1/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,794590,8/1/2016,INPATIENT HOSPITAL,0,,
100001,419136,8/1/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,624909,8/2/2016,PROVIDERS OFFICE,0,,
100001,663935,8/3/2016,AMBULANCE - LAND,0,,
100001,775586,8/3/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,831025,8/3/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,472820,8/3/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,708913,8/3/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,744068,8/5/2016,INPATIENT HOSPITAL,0,,
100001,810119,8/5/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,393492,8/5/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,778691,8/5/2016,INPATIENT HOSPITAL,0,,
100001,568542,8/6/2016,INPATIENT HOSPITAL,0,,
100001,669969,8/7/2016,AMBULANCE - LAND,0,,
100001,637393,8/7/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,399867,8/7/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,557544,8/7/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,812133,8/7/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,700656,8/8/2016,AMBULANCE - LAND,0,,
100001,781734,8/8/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,682172,8/8/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,658111,8/8/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,639308,8/9/2016,INPATIENT HOSPITAL,0,,
100001,698982,8/9/2016,INPATIENT PSYCHIATRIC FACILITY,0,,
100001,617366,8/9/2016,INPATIENT HOSPITAL,0,,
100001,794189,8/9/2016,INPATIENT PSYCHIATRIC FACILITY,0,,
100001,564988,8/10/2016,AMBULANCE - LAND,0,,
100001,743393,8/10/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,566419,8/10/2016,INPATIENT HOSPITAL,0,,
100001,742453,8/10/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,656508,8/10/2016,OUTPATIENT HOSPITAL,0,,
100001,664830,8/10/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,777866,8/11/2016,INPATIENT HOSPITAL,0,,
100001,600116,8/11/2016,OUTPATIENT HOSPITAL,0,,
100001,794193,8/11/2016,OUTPATIENT HOSPITAL,0,,
100001,523448,8/19/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,618537,8/19/2016,OUTPATIENT HOSPITAL,0,,
100001,534929,8/19/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,744892,8/19/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,533105,8/20/2016,INPATIENT HOSPITAL,0,,
100001,476815,8/20/2016,OUTPATIENT HOSPITAL,0,,
100001,761063,8/20/2016,OUTPATIENT HOSPITAL,0,,
100001,802155,8/29/2016,FEDERALLY QUALIFIED HEALTH CENTER,0,,
100001,910274,9/5/2016,INPATIENT HOSPITAL,0,,
100001,1205083,9/5/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,1054668,9/5/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,848851,9/6/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,1020108,9/7/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,971528,9/7/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,902254,9/8/2016,INPATIENT HOSPITAL,0,,
100001,876798,9/8/2016,INPATIENT PSYCHIATRIC FACILITY,0,,
100001,953753,9/8/2016,INPATIENT HOSPITAL,0,,
100001,1121006,9/9/2016,INPATIENT HOSPITAL,0,,
100001,1068934,9/11/2016,INPATIENT HOSPITAL,0,,
100001,863201,9/12/2016,INPATIENT PSYCHIATRIC FACILITY,0,,
100001,1138641,9/13/2016,INPATIENT HOSPITAL,0,,
100001,1167342,9/14/2016,INPATIENT HOSPITAL,0,,
100001,865376,9/15/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,1143605,9/15/2016,OUTPATIENT HOSPITAL,0,,
100001,859692,9/15/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,1110348,9/16/2016,AMBULATORY SURGICAL CENTER,0,,
100001,1193870,9/16/2016,OUTPATIENT HOSPITAL,0,,
100001,1024226,9/16/2016,OUTPATIENT HOSPITAL,0,,
100001,1057258,9/16/2016,OUTPATIENT HOSPITAL,0,,
100001,899177,9/22/2016,FEDERALLY QUALIFIED HEALTH CENTER,0,,
100001,1111501,9/23/2016,INPATIENT HOSPITAL,0,,
100001,1151943,9/23/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,837941,9/24/2016,INPATIENT HOSPITAL,0,,
100001,1235690,9/25/2016,INPATIENT HOSPITAL,0,,
100001,1267985,9/26/2016,INPATIENT HOSPITAL,0,,
100001,1253652,9/29/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,1069309,9/29/2016,OUTPATIENT HOSPITAL,0,,
100001,1006863,9/29/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,1070377,9/30/2016,OUTPATIENT HOSPITAL,0,,
100001,993284,9/30/2016,OUTPATIENT HOSPITAL,0,,
100001,945093,9/30/2016,OUTPATIENT HOSPITAL,0,,
100001,941565,9/30/2016,OUTPATIENT HOSPITAL,0,,
100001,1377121,10/6/2016,INDEPENDENT LABORATORY,0,,
100001,1334821,10/7/2016,INPATIENT HOSPITAL,0,,
100001,1667753,10/7/2016,INPATIENT HOSPITAL,0,,
100001,1607867,10/9/2016,INPATIENT HOSPITAL,0,,
100001,1501098,10/9/2016,INPATIENT HOSPITAL,0,,
100001,1564987,10/9/2016,INPATIENT HOSPITAL,0,,
100001,1592101,10/9/2016,INPATIENT HOSPITAL,0,,
100001,1493599,10/10/2016,INPATIENT HOSPITAL,0,,
100001,1494076,10/11/2016,INPATIENT HOSPITAL,0,,
100001,1508913,10/24/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,1434462,10/24/2016,OUTPATIENT HOSPITAL,0,,
100001,1645073,10/24/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,1516265,10/25/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,1439574,10/25/2016,OUTPATIENT HOSPITAL,0,,
100001,1274327,10/30/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,1558008,10/30/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,1621246,10/30/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,1450398,10/30/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,1878477,11/2/2016,PROVIDERS OFFICE,0,,
100001,2099947,11/9/2016,PROVIDERS OFFICE,0,,
100001,1809179,11/11/2016,FEDERALLY QUALIFIED HEALTH CENTER,0,,
100001,1912064,11/11/2016,FEDERALLY QUALIFIED HEALTH CENTER,0,,
100001,2048910,11/15/2016,AMBULANCE - LAND,0,,
100001,1775205,11/15/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,2077291,11/15/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,1754651,11/15/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,2010677,11/21/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,2108551,11/21/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,1822538,11/23/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,1973820,11/23/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,2070865,11/23/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,1729493,11/23/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,2035516,11/27/2016,INPATIENT HOSPITAL,0,,
100001,1790147,11/27/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,2120114,11/28/2016,INPATIENT HOSPITAL,0,,
100001,1828083,11/28/2016,INPATIENT HOSPITAL,0,,
100001,1760774,11/30/2016,INPATIENT HOSPITAL,0,,
100001,2400391,12/8/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,2444894,12/8/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,2321014,12/21/2016,AMBULANCE - LAND,0,,
100001,2259184,12/21/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,2470788,12/21/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,2486161,12/21/2016,OUTPATIENT HOSPITAL,0,,
100001,2133174,12/21/2016,OUTPATIENT HOSPITAL,0,,
100001,2315959,12/21/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,2515685,12/28/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100001,2221416,12/28/2016,EMERGENCY ROOM - HOSPITAL,0,,
100001,2483353,12/29/2016,INPATIENT HOSPITAL,0,,
100001,2399691,12/29/2016,INPATIENT HOSPITAL,0,,
100001,2449445,12/29/2016,INPATIENT HOSPITAL,0,,
100001,2203368,12/30/2016,INPATIENT HOSPITAL,0,,
100001,2532710,12/30/2016,INPATIENT HOSPITAL,0,,
100002,291967,6/28/2016,INPATIENT HOSPITAL,0,,
100002,12385,6/28/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,171115,6/28/2016,INPATIENT HOSPITAL,0,,
100002,195353,6/28/2016,INPATIENT HOSPITAL,0,,
100002,112293,6/29/2016,INPATIENT HOSPITAL,0,,
100002,195083,7/9/2016,AMBULANCE - LAND,0,,
100002,383416,7/9/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,122694,7/10/2016,INPATIENT HOSPITAL,0,,
100002,166662,7/10/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,117812,7/10/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,237430,7/10/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,109742,7/10/2016,OUTPATIENT HOSPITAL,0,,
100002,237320,7/11/2016,INPATIENT HOSPITAL,0,,
100002,154459,7/11/2016,INPATIENT HOSPITAL,0,,
100002,331948,7/12/2016,INPATIENT HOSPITAL,0,,
100002,351690,7/12/2016,INPATIENT HOSPITAL,0,,
100002,41380,7/13/2016,INPATIENT HOSPITAL,0,,
100002,67637,7/15/2016,AMBULANCE - LAND,0,,
100002,154483,7/15/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,302461,7/15/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,227798,7/15/2016,INPATIENT HOSPITAL,0,,
100002,16383,7/15/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,384717,7/16/2016,AMBULANCE - LAND,0,,
100002,165999,7/16/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,183016,7/16/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,302084,7/16/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,205847,7/16/2016,OUTPATIENT HOSPITAL,0,,
100002,371297,7/17/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,185487,7/17/2016,OUTPATIENT HOSPITAL,0,,
100002,162030,7/17/2016,OUTPATIENT HOSPITAL,0,,
100002,142872,7/17/2016,INPATIENT HOSPITAL,0,,
100002,123408,7/17/2016,OUTPATIENT HOSPITAL,0,,
100002,205189,7/18/2016,INPATIENT HOSPITAL,0,,
100002,136717,7/18/2016,OUTPATIENT HOSPITAL,0,,
100002,384209,7/21/2016,INPATIENT HOSPITAL,0,,
100002,416303,7/22/2016,INPATIENT HOSPITAL,0,,
100002,503903,7/22/2016,AMBULANCE - LAND,0,,
100002,654096,7/22/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,794590,7/22/2016,INPATIENT HOSPITAL,0,,
100002,419136,7/22/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,624909,7/23/2016,PROVIDERS OFFICE,0,,
100002,663935,7/24/2016,AMBULANCE - LAND,0,,
100002,775586,7/24/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,831025,7/24/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,472820,7/24/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,708913,7/24/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,744068,7/26/2016,INPATIENT HOSPITAL,0,,
100002,810119,7/26/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,393492,7/26/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,778691,7/26/2016,INPATIENT HOSPITAL,0,,
100002,568542,7/27/2016,INPATIENT HOSPITAL,0,,
100002,669969,7/28/2016,AMBULANCE - LAND,0,,
100002,637393,7/28/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,399867,7/28/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,557544,7/28/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,812133,7/28/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,700656,7/29/2016,AMBULANCE - LAND,0,,
100002,781734,7/29/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,682172,7/29/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,658111,7/29/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,639308,7/30/2016,INPATIENT HOSPITAL,0,,
100002,698982,7/30/2016,INPATIENT PSYCHIATRIC FACILITY,0,,
100002,617366,7/30/2016,INPATIENT HOSPITAL,0,,
100002,794189,7/30/2016,INPATIENT PSYCHIATRIC FACILITY,0,,
100002,564988,7/31/2016,AMBULANCE - LAND,0,,
100002,743393,7/31/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,566419,7/31/2016,INPATIENT HOSPITAL,0,,
100002,742453,7/31/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,656508,7/31/2016,OUTPATIENT HOSPITAL,0,,
100002,664830,7/31/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,777866,8/1/2016,INPATIENT HOSPITAL,0,,
100002,600116,8/1/2016,OUTPATIENT HOSPITAL,0,,
100002,794193,8/1/2016,OUTPATIENT HOSPITAL,0,,
100002,523448,8/9/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,618537,8/9/2016,OUTPATIENT HOSPITAL,0,,
100002,534929,8/9/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,744892,8/9/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,533105,8/10/2016,INPATIENT HOSPITAL,0,,
100002,476815,8/10/2016,OUTPATIENT HOSPITAL,0,,
100002,761063,8/10/2016,OUTPATIENT HOSPITAL,0,,
100002,802155,8/19/2016,FEDERALLY QUALIFIED HEALTH CENTER,0,,
100002,910274,8/26/2016,INPATIENT HOSPITAL,0,,
100002,1205083,8/26/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,1054668,8/26/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,848851,8/27/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,1020108,8/28/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,971528,8/28/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,902254,8/29/2016,INPATIENT HOSPITAL,0,,
100002,876798,8/29/2016,INPATIENT PSYCHIATRIC FACILITY,0,,
100002,953753,8/29/2016,INPATIENT HOSPITAL,0,,
100002,1121006,8/30/2016,INPATIENT HOSPITAL,0,,
100002,1068934,9/1/2016,INPATIENT HOSPITAL,0,,
100002,863201,9/2/2016,INPATIENT PSYCHIATRIC FACILITY,0,,
100002,1138641,9/3/2016,INPATIENT HOSPITAL,0,,
100002,1167342,9/4/2016,INPATIENT HOSPITAL,0,,
100002,865376,9/5/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,1143605,9/5/2016,OUTPATIENT HOSPITAL,0,,
100002,859692,9/5/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,1110348,9/6/2016,AMBULATORY SURGICAL CENTER,0,,
100002,1193870,9/6/2016,OUTPATIENT HOSPITAL,0,,
100002,1024226,9/6/2016,OUTPATIENT HOSPITAL,0,,
100002,1057258,9/6/2016,OUTPATIENT HOSPITAL,0,,
100002,899177,9/12/2016,FEDERALLY QUALIFIED HEALTH CENTER,0,,
100002,1111501,9/13/2016,INPATIENT HOSPITAL,0,,
100002,1151943,9/13/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,837941,9/14/2016,INPATIENT HOSPITAL,0,,
100002,1235690,9/15/2016,INPATIENT HOSPITAL,0,,
100002,1267985,9/16/2016,INPATIENT HOSPITAL,0,,
100002,1253652,9/19/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,1069309,9/19/2016,OUTPATIENT HOSPITAL,0,,
100002,1006863,9/19/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,1070377,9/20/2016,OUTPATIENT HOSPITAL,0,,
100002,993284,9/20/2016,OUTPATIENT HOSPITAL,0,,
100002,945093,9/20/2016,OUTPATIENT HOSPITAL,0,,
100002,941565,9/20/2016,OUTPATIENT HOSPITAL,0,,
100002,1377121,9/26/2016,INDEPENDENT LABORATORY,0,,
100002,1334821,9/27/2016,INPATIENT HOSPITAL,0,,
100002,1667753,9/27/2016,INPATIENT HOSPITAL,0,,
100002,1607867,9/29/2016,INPATIENT HOSPITAL,0,,
100002,1501098,9/29/2016,INPATIENT HOSPITAL,0,,
100002,1564987,9/29/2016,INPATIENT HOSPITAL,0,,
100002,1592101,9/29/2016,INPATIENT HOSPITAL,0,,
100002,1493599,9/30/2016,INPATIENT HOSPITAL,0,,
100002,1494076,10/1/2016,INPATIENT HOSPITAL,0,,
100002,1508913,10/14/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,1434462,10/14/2016,OUTPATIENT HOSPITAL,0,,
100002,1645073,10/14/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,1516265,10/15/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,1439574,10/15/2016,OUTPATIENT HOSPITAL,0,,
100002,1274327,10/20/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,1558008,10/20/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,1621246,10/20/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,1450398,10/20/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,1878477,10/23/2016,PROVIDERS OFFICE,0,,
100002,2099947,10/30/2016,PROVIDERS OFFICE,0,,
100002,1809179,11/1/2016,FEDERALLY QUALIFIED HEALTH CENTER,0,,
100002,1912064,11/1/2016,FEDERALLY QUALIFIED HEALTH CENTER,0,,
100002,2048910,11/5/2016,AMBULANCE - LAND,0,,
100002,1775205,11/5/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,2077291,11/5/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,1754651,11/5/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,2010677,11/11/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,2108551,11/11/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,1822538,11/13/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,1973820,11/13/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,2070865,11/13/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,1729493,11/13/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,2035516,11/17/2016,INPATIENT HOSPITAL,0,,
100002,1790147,11/17/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,2120114,11/18/2016,INPATIENT HOSPITAL,0,,
100002,1828083,11/18/2016,INPATIENT HOSPITAL,0,,
100002,1760774,11/20/2016,INPATIENT HOSPITAL,0,,
100002,2400391,11/28/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,2444894,11/28/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,2321014,12/11/2016,AMBULANCE - LAND,0,,
100002,2259184,12/11/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,2470788,12/11/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,2486161,12/11/2016,OUTPATIENT HOSPITAL,0,,
100002,2133174,12/11/2016,OUTPATIENT HOSPITAL,0,,
100002,2315959,12/11/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,2515685,12/18/2016,EMERGENCY ROOM - HOSPITAL,0,1,
100002,2221416,12/18/2016,EMERGENCY ROOM - HOSPITAL,0,,
100002,2483353,12/19/2016,INPATIENT HOSPITAL,0,,
100002,2399691,12/19/2016,INPATIENT HOSPITAL,0,,
100002,2449445,12/19/2016,INPATIENT HOSPITAL,0,,
100002,2203368,12/20/2016,INPATIENT HOSPITAL,0,,
100002,2532710,12/20/2016,INPATIENT HOSPITAL,0,,

mahoneypat · ‎01-10-2021

Here is a column expression that should do it. Note that it puts the 1 on the row where the ClaimID is the lowest (for that person and date).

ED Visits =
VAR vThisClaim = visits[CLAIMID]
VAR vPrevVisitsThisDay =
    CALCULATE (
        COUNTROWS ( visits ),
        ALLEXCEPT (
            visits,
            visits[MEM_UNQ_ID],
            visits[SERVICE_START_DATE],
            visits[PLACE_OF_SERVICE]
        ),
        visits[CLAIMID] < vThisClaim
    )
RETURN
    IF (
        AND (
            visits[PLACE_OF_SERVICE] = "EMERGENCY ROOM - HOSPITAL",
            ISBLANK ( vPrevVisitsThisDay )
        ),
        1,
        BLANK ()
    )

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

View solution in original post

mahoneypat · ‎01-11-2021

Here is a different way to calculate this column. You can check if it is more performant. If not, you can always make a DAX table to add to your model that summarizes this table one time, and you can just do a simple COUNTROWS to get your result (DAX below).

A reminder that it may be better to just use a measure instead of either a DAX column or table.

ED Visits 2 =
VAR vThisDate = visits[SERVICE_START_DATE]
VAR vThisID = visits[MEM_UNQ_ID]
VAR vThisPlace = visits[PLACE_OF_SERVICE]
VAR vMinClaimID =
    CALCULATE (
        MIN ( visits[CLAIMID] ),
        ALL ( visits ),
        visits[SERVICE_START_DATE] = vThisDate,
        visits[MEM_UNQ_ID] = vThisID,
        visits[PLACE_OF_SERVICE] = vThisPlace
    )
VAR vResult =
    IF (
        vThisPlace = "EMERGENCY ROOM - HOSPITAL",
        IF (
            visits[CLAIMID] = vMinClaimID,
            1
        )
    )
RETURN
    vResult

DAX Table

VisitSummary =
SUMMARIZE (
    FILTER (
        visits,
        visits[PLACE_OF_SERVICE] = "EMERGENCY ROOM - HOSPITAL"
    ),
    visits[MEM_UNQ_ID],
    visits[SERVICE_START_DATE]
)

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

View solution in original post

parry2k · ‎01-10-2021

@Anonymous @mahoneypat already provided a solution, why not you first test it, and if it works out, awesome, if not then I will look further into it.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

mahoneypat · ‎01-10-2021

Here is a column expression that should do it. Note that it puts the 1 on the row where the ClaimID is the lowest (for that person and date).

ED Visits =
VAR vThisClaim = visits[CLAIMID]
VAR vPrevVisitsThisDay =
    CALCULATE (
        COUNTROWS ( visits ),
        ALLEXCEPT (
            visits,
            visits[MEM_UNQ_ID],
            visits[SERVICE_START_DATE],
            visits[PLACE_OF_SERVICE]
        ),
        visits[CLAIMID] < vThisClaim
    )
RETURN
    IF (
        AND (
            visits[PLACE_OF_SERVICE] = "EMERGENCY ROOM - HOSPITAL",
            ISBLANK ( vPrevVisitsThisDay )
        ),
        1,
        BLANK ()
    )

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Anonymous · ‎01-10-2021

@mahoneypat thank you for this, i think it would work! but i'm working with about 12M rows and keep getting an out of memory error - anything you can think of that would help with memory optimization?

mahoneypat · ‎01-11-2021

Here is a different way to calculate this column. You can check if it is more performant. If not, you can always make a DAX table to add to your model that summarizes this table one time, and you can just do a simple COUNTROWS to get your result (DAX below).

A reminder that it may be better to just use a measure instead of either a DAX column or table.

ED Visits 2 =
VAR vThisDate = visits[SERVICE_START_DATE]
VAR vThisID = visits[MEM_UNQ_ID]
VAR vThisPlace = visits[PLACE_OF_SERVICE]
VAR vMinClaimID =
    CALCULATE (
        MIN ( visits[CLAIMID] ),
        ALL ( visits ),
        visits[SERVICE_START_DATE] = vThisDate,
        visits[MEM_UNQ_ID] = vThisID,
        visits[PLACE_OF_SERVICE] = vThisPlace
    )
VAR vResult =
    IF (
        vThisPlace = "EMERGENCY ROOM - HOSPITAL",
        IF (
            visits[CLAIMID] = vMinClaimID,
            1
        )
    )
RETURN
    vResult

DAX Table

VisitSummary =
SUMMARIZE (
    FILTER (
        visits,
        visits[PLACE_OF_SERVICE] = "EMERGENCY ROOM - HOSPITAL"
    ),
    visits[MEM_UNQ_ID],
    visits[SERVICE_START_DATE]
)

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

parry2k · ‎01-10-2021

@Anonymous can you put a sample pbix file and share it thru one drive/google drive with the expected output and I will get it done.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎01-10-2021

Sample Data in CSV format is at the end of my post with current output from my calculated column (ED Visits Formula) and expected output (Expected ED Visits). I'll work on getting that into a sharable PBIX and post it shortly as well.

Anonymous · ‎01-10-2021

@parry2k here is a link to a pbix file, please let me know if you have any trouble accessing.

Emergency Room Visits PBIX

parry2k · ‎01-09-2021

@Anonymous add this measure

ED Visits =
CALCULATE( 
DISTINCTCOUNT ( 'Fact tbl_claims'[Patiend ID] ), --change whatever column need to be unique
'Fact tbl_claims'[Place of Service] = "EMERGENCY ROOM - HOSPITAL"
)

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎01-10-2021

@parry2k thank you for the suggestion, but i don't think that will work for my use case. sorry for not including it in my previous post, but i need to be able to filter and create additional aggregations for this so i really need it to be a calculated column, not a measure.

i think i would need a column that stores a 1 if the claim is the first instance of that particular patient visiting the emergency room that day and a 0 if not. thanks for the help!

Ashish_Mathur · ‎01-12-2021

Hi,

How would you know which thw first instance for a patient on a particular day is unless you have the time stamp?

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

DAX Formula Help > Count one instance of patient visit per day

Helpful resources

Power BI Monthly Update - September 2024

Microsoft Fabric & AI Learning Hackathon

Fabric Community Update - September 2024

How to Get Your Question Answered Quickly