Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
mikegrad
New Member

struggling with count of specific rows against count of all rows

sorry for the title - nto sure if this is the best way to describe it. Im relatively new to powerBI, but not new to SQL, so sometimes i struggle to get my head around how to do things in powerBI. I feel like my current problem should be easy, but i cant figure it out. 

 

i am creating a visual that i want to show the count of events a member attended vs the total # of events. this is important as members need to attent X percent or number of events per month. 

 

there are two main tables, Events and MemberEvents. Events contains every event, has event ID, date, etc. MemberEvents has eventID, MemberID which has a row for eceh event the member attended.

 

for example, John Smith went to 4 of 10 events so far this month. I want to show this with his 4 events against the total, either as absolute numbers in a stacked column, or even pie chart. my current stacked column chart has the Count of the event from memberEvents filtered by a slicer where you can select the member. This works, and shows the total of 4. where i am failing is how to get the 10 in, or the slice representing the 6 john didnt attend. I cant seem to get the total count of events in there no matter what i do.  I have tried pulling in the count of events from Events, but it still filters based on member name due to the relationship on eventID between tables and just show the 4 count again. 

 

I hope this is a good explanation of what i am trying to acomplish. Any help is appreciated!

 

 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

Ah, it is the bi-directional relationship that is causing your event count to get filtered.  Set the releationship between event and member event to only singe direction and your total event count should work.

jdbuchanan71_0-1707853576595.png

 

View solution in original post

10 REPLIES 10
jdbuchanan71
Super User
Super User

Ah, it is the bi-directional relationship that is causing your event count to get filtered.  Set the releationship between event and member event to only singe direction and your total event count should work.

jdbuchanan71_0-1707853576595.png

 

excellent, thank you.  looks like i had another issue between the roster table as well, and i corrected that and now it looks the way I envisioned! 

jdbuchanan71
Super User
Super User

I was actually looking for this layout, easier to read for me.

jdbuchanan71_0-1707846923174.png

 

 

sorry, here it is

Screenshot 2024-02-13 131252.jpg

jdbuchanan71
Super User
Super User

Can you paste a picture of your data model?  It might be a problem with one of the realationship filter settings.

mikegrad
New Member

to be completely clear there are other entries in the memberEvents table, but ive used a slicer to select member id 1150

mikegrad
New Member

Data looks like this. there is alot more data in each table but this is the key columns

Events

EventIDEventDate
2024-000022-E1/25/2024
2024-000008-E1/8/2024
2024-000009-E1/9/2024
2024-000011-E1/10/2024
2024-000015-E1/15/2024
2024-0000021/2/2024
2024-0000041/4/2024
2024-0000051/5/2024
2024-0000061/5/2024
2024-0000071/5/2024

 

MemberEvents

EventIDMemberID
2024-000022-E1150
2024-000008-E1150
2024-000009-E1150
2024-000011-E1150
Ashish_Mathur
Super User
Super User

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.


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

@mikegrad 

You will need a Dates table that links to your events table, you can make one like this.

Dates = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Month Year",EOMONTH([Date],0)
)

Then a measure to count events.

Event Count = COUNTROWS ( Events )

One to count events members attended

Events Attended = COUNTROWS ( MemberEvents )

And ones to find the count of missed events.

Events Missed = [Event Count] - [Events Attended]

jdbuchanan71_0-1707688516998.png

The relationships in the model are pretty straight forward.

jdbuchanan71_1-1707688558454.png

I have attached my sample file for you to look at.

 

 

thanks jbuchanan71, this is exactly what i am trying to do, but this did not work. Im still getting the 4 total count, because its still filtering. let me post the data i am working with and see if that helps.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

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.