Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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,,
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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.
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.
@parry2k here is a link to a pbix file, please let me know if you have any trouble accessing.
Emergency Room Visits PBIX
@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.
@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?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
107 | |
97 | |
39 | |
32 |
User | Count |
---|---|
153 | |
122 | |
77 | |
74 | |
44 |