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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
FMPE
Frequent Visitor

Creating relationships between tables that contain duplicate values

Hi!

 

First of all, I'm fairly new to Power BI, so if I've got a concept completely wrong, or I'm approaching something in a silly way, please do let me know - I'm trying to learn!

 

So I've got 4 tables; the first two them are "raw" data I am sent once a month, and the last two are tables I've created for the report:

  • Table 1 - "Master": a list of incidents with that caused some machine to fail, including a number of details including the machine ID (a unique identifier), the Type (which type of machine it is) and the dates for both the occurence of the incident and when it was fixed.
  • Table 2 - "Machine data": a list comprised of information about a given machine and whether or not is it in use. This list is formatted in such a way so that every month, data for every active machine is appended, e.g. the name, the date of the month for which it has been active and a few other details.
  • Table 3 - "Active machine data":  basically a slice of table 1 including just the last month, to have a table that includes just the machines that are currently active. I created this table to have a table with unique IDs as to create a relationship with the Master table (Table 1) from which I can easilly do analysis.
  • Table 4 - "Calendar table": Fairly self-explanatory - a calendar table connected up with the Master table to perform slicing, etc.

 

What I'm trying to do, and have achieved successfully using a relationship between Table 1 & 3, is calculating and displaying the number of "off days" for each machine for each month. Doing the most recent month is easy, as I'm able to use table 3 which has unique machine IDs meaning that I can create a relationship bewteen it and the master table. However, now that I'm trying to do the same with the historic data, I run into trouble as the duplicated IDs in table 2 doesn't allow for me to create a relationship between it and the master table (which was the whole reason for creating table 2 in the first place).

 

So, basically my question is: how do I go about this the best way? Should I merge the tables somehow, alter either tables, create a many-to-many relationship, or something entirely different?

 

Again, apologies for the potentially stupid question and/or explaination. If I was unclear, please tell me what to clarify / expand upon and I'll try my best to do so.

Thanks in advance!

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @FMPE ,

According to your description, it seems that you got some problem when created the relationships between tables. What's your final expected result? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

 

In addition, you can refer the following links to create relationship:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

 

Creating Relationship Based on Multiple Fields in Power BI Using TREATAS DAX Function - RADACAD

 

Solved: Relationships between tables when both have duplic... - Microsoft Fabric Community

Best Regards

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

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @FMPE ,

According to your description, it seems that you got some problem when created the relationships between tables. What's your final expected result? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

 

In addition, you can refer the following links to create relationship:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

 

Creating Relationship Based on Multiple Fields in Power BI Using TREATAS DAX Function - RADACAD

 

Solved: Relationships between tables when both have duplic... - Microsoft Fabric Community

Best Regards

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

Apologies for the late reply, I have been figuring things out and learning a lot since I made the post, and I somehow overlooked that there had been a reply here. Unfortunately, I am not able to share the .pbix, as the data isn't confidential.

I am going to mark your response as an answer, as my problem has developed into something else entirely, and I'll make a new post for adressing that, instead of editing this one.

Thanks!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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