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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Reine
Helper IV
Helper IV

How 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.

sample data.PNG

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 🙂

result.PNG



1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

 

 

View solution in original post

7 REPLIES 7
HotChilli
Super User
Super User

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 🙂

HotChilli
Super User
Super User

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  🙂

HotChilli
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors