Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
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) |
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 |
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 |
still looks weird but basically 544078425 has 2, 544078424 has 0, and 544078423 has 1.
Sure I just need to scrub some data out.
and if you use a table visual with the column [Service point] in the rows with the measure Countrows(premise)?
Same thing
can you send a screenshot of the relationship from the model View?
Countrows(premise) just gives the total number or premises, not the count of premises that are related to a given service point.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |