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

[Geld]

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.

 

[mangaus1111 · Accepted solution]

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.

[Geld]

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]

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

[Geld]

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)
544078425	2
544078424	0
544078423	1
544078422	1
544078421	1
544078420	0
544078419	1
544078418	1
544078417	1
544078416	1
544078415	0
544078414	1
544078413	0
544078412	1
544078411	1
544078410	2
544078409	3
544078408	1
544078407	1
544078406	1
544078405	1
544078404	1

[Geld]

I incorectly formatted the expected results table:

ID	Count of Service_ID
544078425	2
544078424	0
544078423	1
544078422	1
544078421	1
544078420	0
etc...
544078410	2
544078409	3

[Geld]

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

[Geld]

Sure I just need to scrub some data out.

[mangaus1111]

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

[Geld]

Same thing 

[mangaus1111]

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

[Geld]

 

[mangaus1111]

Hi @Geld ,

have you already tried COUNTROWS('Nameofthetable')?

[Geld]

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