Forum Discussion
Helper IVHow to count when data is in more than one row
Hi - I'm trying to get a count of how many "bills" have certain combinations of codes billed on the same visit, but I cannot figure out how to get a count when the data is in more than one row. See sample data below.
For example, I want to count how many bills had both a 92004 and 92015 code billed with the same BillID. In the below data sample that would be 6.
Ultimately I need to count how many 92015 codes were billed at the same time as each of the other codes, as well as get a percentage rate for each (see below), and add to a Matrix by month and provider, but I think I can figure out the rest if I first get help to count the visits with the criteria being in more than one row. I appreciate any help with this. Thank you 🙂
Thank you for the prompt, I had not forgotten about you.
This is a measure.TestNoOf92004_92015 = var _04 = CALCULATETABLE(TableX, TableX[Code] = 92004) var _sel04 = SELECTCOLUMNS(_04, "Bills", TableX[BillID]) var _15 = CALCULATETABLE(TableX, TableX[Code] = 92015) var _sel15 = SELECTCOLUMNS(_15, "Bills", TableX[BillID]) RETURN COUNTROWS(INTERSECT(_sel04, _sel15))Let me know how you get on
7 Replies
- HotChilli
Community Champion
Thank you for the prompt, I had not forgotten about you.
This is a measure.TestNoOf92004_92015 = var _04 = CALCULATETABLE(TableX, TableX[Code] = 92004) var _sel04 = SELECTCOLUMNS(_04, "Bills", TableX[BillID]) var _15 = CALCULATETABLE(TableX, TableX[Code] = 92015) var _sel15 = SELECTCOLUMNS(_15, "Bills", TableX[BillID]) RETURN COUNTROWS(INTERSECT(_sel04, _sel15))Let me know how you get on
ReineThank you so much, I really appreciate your help! This did what was intended, now on to tackle the rest of the problem 🙂
- HotChilli
Post the sample data as text please.
- Reine
Sorry, HotChilli here you go
ChargeID BillID ProviderID Date Code 3856353 734351 423536 8/1/2023 92004 3856357 734353 423536 8/1/2023 92004 3856358 734353 423536 8/1/2023 92015 3856497 734382 208568 8/1/2023 92014 3856544 734400 423536 8/1/2023 92014 3856546 734400 423536 8/1/2023 92015 3856554 734403 423536 8/1/2023 92004 3856555 734403 423536 8/1/2023 92015 3856775 734439 365981 8/1/2023 92014 3857119 734533 299 8/1/2023 92014 3858663 734627 286193 8/2/2023 92004 3858826 734686 423536 8/1/2023 92004 3858827 734686 423536 8/1/2023 92015 3881483 738736 343720 8/14/2023 92014 3881484 738736 343720 8/14/2023 92015 3931563 746532 423536 9/11/2023 92004 3931564 746532 423536 9/11/2023 92015 3946410 749352 286193 9/14/2023 99205 3998985 757354 343720 10/18/2023 92014 3998986 757354 343720 10/18/2023 92015 4001922 757940 299 10/19/2023 92014 4031832 763254 259766 11/8/2023 92015 4031831 763254 259766 11/8/2023 99204 4032594 763499 286193 11/2/2023 92015 4032593 763499 286193 11/2/2023 99205 4042105 765015 278985 11/15/2023 99204
- HotChilli
Thanks for the data.
"In the below data sample that would be 6." Is it not 4?