Relationship between tables

[quipmaster]

Hi Power BI Super Community,

I am unable to establish a relationship between my tables. I have tried using a star schema. A fact table and the dim tables.

However, the star schema does not work as expected with a filter in the CA#1 tab.

Below is the PBIX file. Fingers crossed that the link works.

Tutoring Only_V2.pbix

Thank you for any nudge you are able to provide.

Luis

[Ashish_Mathur]

Hi,

Your requirement is not at all clear.  From the looks of it, Student ID is the common column in all tables.  Create a many to one relationship from 3 tables to 1 master table (the 1 master table is the one which has all ID's without repetitions). 

[quipmaster]

Hi @Ashish_Mathur 

I've updated my model per your suggestion.

Tutoring Only_V2.pbix

The issue is that when I make Teacher-Student the fact table, I get an error for the scatterplot in tab Tutoring. Please see tab. The error is that no relationship exists. Under this situation, the scatterplot only displays if I sum tutor hours. I don't want to summarize. 

However, if I make a relationship between CA#1 and Tutor Hours by Sessions, the filter for Session Weeks (Group) does not activate.

In sum, I want the to achieve the following.

1. Display scatter plot by non-summarize CA#1 Scores and non-summarized Tutor Hours

2. Have a Session Weeks (Groups) Filter for both the column chart and the scatter plot on Tutoring tab.

But my current relationship model is not working as intended.

Please let me know if you have any questions.

[Ashish_Mathur]

Hi,

I still do not understand what you want but from whatever i can see if this is any helpful.  Bring over the hours from the Tutorial log_long table to the CA table with this calculated column formula

Column = CALCULATE(SUM('Tutorial Log_Long'[Total Hours]),FILTER('Tutorial Log_Long','Tutorial Log_Long'[Student ID]=EARLIER('CA #1_Long'[Student ID])))

To your scatter plot visual, drag this column to the X-axis.

[quipmaster]

Hi @Ashish_Mathur ,

Thank you for the formula. It works. 

However, my original suspicion was that my relationships were set up incorrectly. Tutorial Log_Long, CA#1_Long, Tutor Hours by Session are connected to Teacher-Student List (Fact Table). Teacher-Student List has unduplicated student ids.

My initial thought was that by connecting all three tables to my fact table, that i would be able to create slicers for Sessions Weeks (groups) to control both graphs.

When i use your DAX for column, i get the following filter that i cannot remove. Total Tutor Hours is 3.0. I want all Total Tutor Hours to be used.

Tutoring Only_V2.pbix

[Ashish_Mathur]

Hi,

I did not apply any filter in the file that i shared with you.

[quipmaster]

Hi @Ashish_Mathur ,

Thank you for your reply. Could you link or upload your PBIX?

I want to check my PBIX against your PBIX.

Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

i just download the file which you shared with me on Satuday and the calculated formula which i suggested to you is already there.  I downloaded and opened that file - everything is just fine and there are no filters on the scatter plot visual.

[quipmaster]

Hi @ashish ,

Thank you for your reply.

I must have seen a filter that was not there.

At any rate, this time, I dragged in Tutor Hours by Sessions [Sessions Weeks (Groups)]. But it has no effect on the scatterplot because there is no relationship. 

The tables are set up with bi-directional relationships. 

If the Sessions Weeks (Group) filtered the scatterplot, then the y-axis would be 8.0.

If you could let me know what I am missing to make the Tutor Hours by Sessions and CA #1_Long have a relationship, it would be most appreciated.

Tutoring Only_V2.pbix

Luis

[Ashish_Mathur]

Hi,

I probably cannot understand your question.  Someone who does will help you.

[quipmaster]

You got me this far without understanding me. Appreciate your effort. 

[Ashish_Mathur]

You are welcome.

[quipmaster]

The Scatterplot should output like this.

Y-Axis 0 - 8 

X- Axis 0% - 100%

The Calculated measure column, it is outputting like the graph below.

Tutoring Only_V3.pbix