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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
amabrenda1986
Helper II
Helper II

Count Occurrence of a data in another table to display in a table visual

I have two tables that are related 

Intake Table  
Incident idDate CreatedDate ReceivedIncident TypeDate Completed
GRV-00014/1/20232/12/2022Enquiry6/15/2023
GRV-00021/23/202212/16/2021Problem5/12/2023
GRV-00039/18/20235/17/2023Application 
GRV-00043/25/20231/21/2023Application 
GRV-00054/12/20231/15/2022Problem7/10/2023
GRV-00069/15/20224/12/2022Enquiry 

 

Agreement Table 
Agreement IDIncident IdAgreemt Typecreated date
AGR-00001GRV-0001steward 
AGR-00006GRV-0001utility 
AGR-00007GRV-0001provincial 
AGR-00002GRV-0006others 
AGR-00003GRV-0003utility 
AGR-00004GRV-0003provincial 
AGR-00008GRV-0005steward 
AGR-00009GRV-0005utility 
AGR-00010GRV-0005steward 

 

What I want in a Table Visual
Incident idNumber of associated Agreements
GRV-00013
GRV-00020
GRV-00032
GRV-00040
GRV-00053
GRV-00061

 

What measure or new column formula can i use to achieve what i want please?

1 ACCEPTED SOLUTION

Hi @amabrenda1986 ,

 

Please try:

Number of associated Agreements = CALCULATE(COUNTROWS('Agreement'),'Agreement'[Incident Id]=MAX('Intake'[Incident id])) + 0

vcgaomsft_0-1693964079717.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Create a many to One (Single) relationship from Table2 to Table1.  Drag IncidentID from Table1.  Write this measure

Measure = countrows(Areement[Agreement ID])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
Super User

@amabrenda1986 coz of the relationship.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@amabrenda1986 add new measure and in a table visual , use incident id from intake table and this new measure:

 

Count = COUNTROWS ( AgreementTable ) + 0


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

it didn't work, because its counting the total number of agreements instead of counting the agreements associated with each id. Pleae look at the table again. 

 

appreciate your help

Hi @amabrenda1986 ,

 

Please try:

Number of associated Agreements = CALCULATE(COUNTROWS('Agreement'),'Agreement'[Incident Id]=MAX('Intake'[Incident id])) + 0

vcgaomsft_0-1693964079717.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

how would this match to the respective ID in the table visual please?

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

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.