cancel
Showing results 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.

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.

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 🙂

1 ACCEPTED SOLUTION
Super User

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

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

Helper IV

Thank you so much, I really appreciate your help!  This did what was intended, now on to tackle the rest of the problem 🙂

Super User

Thanks for the data.

"In the below data sample that would be 6." Is it not 4?

Helper IV

@HotChilli sorry, you are right.  6 that are 92004 only and 4 that have both.  It's been a long week  🙂

Super User

Post the sample data as text please.

Helper IV

Hi @HotChilli  - did you have a chance to look at this?  do you think it is doable?

Helper IV

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

Announcements

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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors