Count of records in two tables

[htaylor2]

I have two tables,

graduates

academic year

grad term

school

major

degree level

student id

outcomes

academic year

graduation term

school

major

degree level

student id

more rows related to responses to survey

I have slicers for Academic Year, Graduation Term, School, and Major.

I have created the following relationships:

graduates (grad term) to outcomes (graduation term) - this is the active one

outcomes (major) to graduates (major)

outcomes (school) to graduates (school)

My problem, when I select Academic Year and/or graduation term in the respective slicer, both the Response count and the Graduates count change to the correct value. If I select School or Major the Response count is correct but the Graduates count is not. I've tried creating a DAX formula using the CALCULATE function and USERELATIONSHIP but wither I'm not doing it correctly, or it isn't what I need to do. Any help is appreciated.

[amitchandak]

@htaylor2 , Can you share sample data and sample output in table format?

[htaylor2]

Here is a screen shot of the report,

Knowledge rate is just (Responses/Graduates)*100. The values for Responses, Graduates, and Knowledge Rate seem to change correctly when Academic Year and Gradaution Term are selected, not when School or Major is selected.

Here is a link to sample data (200 records) in a spreadsheet,

https://drive.google.com/file/d/1kH0gGzdwhfQtdBbDA6_HTG2YEYorAwQe/view?usp=sharing

Thanks for any help.

[v-zhenbw-msft]

Hi @htaylor2 ,

Maybe your table contains many-to-many relationship.

So we suggest to create dim table to be slicer.

We can create four dim tables and create one-to-many relationships with your two fact table. Each table has one column and unique value.

major slicer = VALUES(graduates[Major])

School slicer = VALUES(graduates[School])

Term slicer = VALUES(graduates[Grad Term])

Year slicer = VALUES(graduates[Academic Year])

Then create slices using these table columns.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[htaylor2 · Accepted solution]

Thank you! The solution you provided didn't work the way I needed it to, but it pointed me in the direction of the solution. I created the following tables as slicers:

• AY slicer = DISTINCT(UNION(SELECTCOLUMNS(graduates,"Academic Year",[Academic Year]),SELECTCOLUMNS(outcomes,"Academic Year",[Academic Year])))
• Grad Term Slicer = DISTINCT(UNION(SELECTCOLUMNS(graduates,"Graduation Term",[Grad Term]),SELECTCOLUMNS(outcomes,"Graduation Term",[graduation term])))
• School Slicer = DISTINCT(UNION(SELECTCOLUMNS(graduates,"School",[School]),SELECTCOLUMNS(outcomes,"School",[school])))
• School Slicer = DISTINCT(UNION(SELECTCOLUMNS(graduates,"School",[School]),SELECTCOLUMNS(outcomes,"School",[school])))

This worked to a point, but I lost the relationships between Academic Year-Graduation Semester and School-Major, so I created the following two slicers:

• AY-Term slicer = DISTINCT(UNION(SELECTCOLUMNS(graduates,"Academic Year",[Academic Year],"Graduation Term",[Grad Term]),SELECTCOLUMNS(outcomes,"Academic Year",[Academic Year],"Graduation Term",[graduation term])))
• School-Major Slicer = DISTINCT(UNION(SELECTCOLUMNS(graduates,"School",[School],"Major",[Major]),SELECTCOLUMNS(outcomes,"School",[school],"Major",[major])))

I'm not sure how to show the relationships in table format as you did, but after creating 10 relationships and using the following slicers, it is working.

• AY-Term slicer
• Grad Term Slicer
• School-Major Slicer
• Major Slicer

I wouldn't have figured this out without your suggestion, thanks again. BTW, I downloaded the pbix but couldn't open it. Got a message that I needed to update PowerBI desktop, but I am using the July version and no update is available as far as I can see.

Hugh

[v-zhenbw-msft]

Hi @htaylor2 ,

Glad you found the solution yourself.😊

You can open the PBIX using Power BI Desktop from the Microsoft Store.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[htaylor2]

Couldn't have done it without your help, you pointed me in the right direction.

[harshnathani]

Hi @htaylor2 ,

Pls share the measure you are using.

Regards,

Harsh Nathani

[htaylor2]

I am using Total Reponses = COUNT(outcomes[Student ID]) and Total Graduates = COUNT(graduates[Student ID])