Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 🙂
Solved! Go to Solution.
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
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
Thank you so much, I really appreciate your help! This did what was intended, now on to tackle the rest of the problem 🙂
Thanks for the data.
"In the below data sample that would be 6." Is it not 4?
@HotChilli sorry, you are right. 6 that are 92004 only and 4 that have both. It's been a long week 🙂
Post the sample data as text please.
Hi @HotChilli - did you have a chance to look at this? do you think it is doable?
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 |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
77 | |
74 | |
57 | |
38 | |
33 |
User | Count |
---|---|
70 | |
63 | |
55 | |
48 | |
46 |