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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Geld
Frequent Visitor

Count of "one" in "many table" of many to one relationship

I have a many to one relationship between two databases, linked by “ID”.

One “service point” has many “premises”.

In Power BI I am trying to make an automated dashboard that counts how many “premises” are in a “service point” including service points that have 0 premises.

Is there a way to use the count function or similar for the related “premises”?

 

I have only figured out how to get the “service point” to show up related in the “premise” table. Below is a visualization of what I’d ideally want.

examplepowerbi.png

1 ACCEPTED SOLUTION
mangaus1111
Solution Sage
Solution Sage

Hi @Geld ,

see my pbi file and let me know if this is your expected result.

https://1drv.ms/u/s!Aj45jbu0mDVJi29DLorhWNDibhx_?e=9oRrnu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
mangaus1111
Solution Sage
Solution Sage

Hi @Geld ,

see my pbi file and let me know if this is your expected result.

https://1drv.ms/u/s!Aj45jbu0mDVJi29DLorhWNDibhx_?e=9oRrnu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I couldn't click you solution at work. I'm sure it worked I forgot to look at home but I ended up putting this in a new column in the "one" table, if someone else stubles apon this.

 

CountMany =
    calculate(
        count(Many[ID])
        ,RELATEDTABLE(Many)
    )+0
mangaus1111
Solution Sage
Solution Sage

if the table service point is related with the table premises the column [Service point] should filter the rows of the table premises.

 

If you can send some sample data in text form, it is all simple and I can prepare a pbi file for you

Service_ID (premise)
544078425
544078423
544078422
544078421
544078419
544078418
544078417
544078416
544078414
544078412
544078411
544078410
544078409
544078408
544078407
544078406
544078405
544078404
544078409
544078425
544078410
544078409

 

 

ID (service point)
544078425
544078424
544078423
544078422
544078421
544078420
544078419
544078418
544078417
544078416
544078415
544078414
544078413
544078412
544078411
544078410
544078409
544078408
544078407
544078406
544078405
544078404

 

Expected results:

ID (service)Count of Service_ID (premise)
5440784252
5440784240
5440784231
5440784221
5440784211
5440784200
5440784191
5440784181
5440784171
5440784161
5440784150
5440784141
5440784130
5440784121
5440784111
5440784102
5440784093
5440784081
5440784071
5440784061
5440784051
5440784041
Geld
Frequent Visitor

I incorectly formatted the expected results table:

IDCount of Service_ID
5440784252
5440784240
5440784231
5440784221
5440784211
5440784200
etc... 
5440784102
5440784093
Geld
Frequent Visitor

still looks weird but basically 544078425 has 2, 544078424 has 0, and 544078423 has 1.

Sure I just need to scrub some data out.

mangaus1111
Solution Sage
Solution Sage

and if you use a table visual with the column [Service point] in the rows with the measure Countrows(premise)?

visual.PNG

Same thing 

can you send a screenshot of the relationship from the model View?

relationship.png

mangaus1111
Solution Sage
Solution Sage

Hi @Geld ,

have you already tried COUNTROWS('Nameofthetable')?

 

 

Countrows(premise) just gives the total number or premises, not the count of premises that are related to a given service point.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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