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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

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

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,,

  

2 ACCEPTED SOLUTIONS
mahoneypat
Microsoft Employee
Microsoft Employee

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

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

10 REPLIES 10
parry2k
Super User
Super User

@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
Microsoft Employee
Microsoft Employee

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
Not applicable

@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?

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

@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
Not applicable

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
Not applicable

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

Emergency Room Visits PBIX 

parry2k
Super User
Super User

@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
Not applicable

@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!

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/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.