struggling with count of specific rows against count of all rows

[mikegrad]

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!

[jdbuchanan71 · Accepted solution]

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.

[mikegrad]

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]

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

[mikegrad]

sorry, here it is

[jdbuchanan71]

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

[mikegrad]

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

[mikegrad]

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

Events

EventID	EventDate
2024-000022-E	1/25/2024
2024-000008-E	1/8/2024
2024-000009-E	1/9/2024
2024-000011-E	1/10/2024
2024-000015-E	1/15/2024
2024-000002	1/2/2024
2024-000004	1/4/2024
2024-000005	1/5/2024
2024-000006	1/5/2024
2024-000007	1/5/2024

MemberEvents

EventID	MemberID
2024-000022-E	1150
2024-000008-E	1150
2024-000009-E	1150
2024-000011-E	1150

[Ashish_Mathur]

Hi,

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

[jdbuchanan71]

@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]

The relationships in the model are pretty straight forward.

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

[mikegrad]

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.