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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Relationship between tables

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

1 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.

Untitled.png


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

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User
Super User

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). 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur ,

 

Thank you for the formula. It works. 

 

quipmaster_0-1679246471438.png

 

 

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.

 

quipmaster_1-1679246549404.png

 

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.

 

quipmaster_2-1679247339892.png

 

Tutoring Only_V2.pbix

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

The Scatterplot should output like this.

Y-Axis 0 - 8 

X- Axis 0% - 100%

quipmaster_0-1679370082862.png

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

 

quipmaster_1-1679370188881.png

 

Tutoring Only_V3.pbix

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.