Table Relationship Issues

[szub]

Hello,

I have a sales by serial number and claims by serial number table and the only common field is the serial number.  Both tables can have the serial number listed more than once for various reasons, so I created a unique key table with the serial numbers from the sales table only listed once and created a relationship to both tables.  When I try to create visuals with information from both tables, I recieve an error that it can't display the visual because it can't determine the relationship between two or more fields. 

I am looking for suggestions on how I can get claim data visuals for serial numbers sold.  I have tried to use LOOKUPVALUE and RELATED or RELATEDTABLE without success, but maybe I am doing something incorrectly.

Any suggestions or guidance will be appreciated.

Thanks in advance

[v-juanli-msft]

Hi @szub 

Modified "SERIAL_NBR" column of your "TransactionData" table as below

Create relationships

srial 1 has 1 row record in "Claim" table, serial 1 has 2 rows in "Transaction" table, so wen adding columns from two tables in to a visual, it shows all data from two tables like crossing join.

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

[szub]

Hi @v-juanli-msft ,

Thank you for your response.  I realized that I did not have a serial number listed more than once in my sample Sales Transaction data once I had left for the day and have now corrected.  Thank you for realizing that.   

All the training and dicussions I have had with others have advised to stay away using many to many relationships, which is why I did not use it.  Are there any negatives that you have experienced with using Many to Many?

Thank you

[v-juanli-msft]

Hi @szub 

Your screenshot shows a workaround for many to many relationships  for two tables in power bi.

Please see differences between two methods:

Use relationships with a many-many cardinality

your workaround

One big disadvantage is many to many relationship may slow the performance for the report.

For specific scenario, please use Performance Analyzer to examine report element performance and decide which method to use.

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

[sturlaws]

could  you also make a mockup in excel of how you want your table/output to look like?

[szub]

Hi @sturlaws ,

I want to be able to create a visual with detailed claim information only for each transaction serial number that has a claim, like v-juanli-msft replied with. Then, I will need to be able to apply filters from either the transaction or claim table to see the applicable data.  I will also be creating quite a few measures like % of serial number sold had claims within xx time frame and do measures/counts on the different claim types.  The more I read on the many to many relationship, I think this will work.  I am currently trying to validate data to make sure it is correct with the many to many relationship.

Thank you - szub

[sturlaws]

Hi @szub,

do you have a screen shot of your model? Or could you share the report?(upload to onedrive/dropbox/other and share the link).

Cheers,
Sturla

[szub]

Hi @sturlaws,

Here are the relationships between the tables:

Thank You

[VasTg]

@szub 

Provide sample data for transaction and claims tables.

[szub]

Hello @VasTg ,

Here is sample Claim Data:

Claim #

Claim Date

Claim Type

Status

Serial #

Svc Agent

Customer

Fail Date

Repair Date

# Trips

Labor

Parts

Other

Total

Claim 1

10/21/2019

Warranty

Paid

Serial 1

SA1

Cust1

10/15/2019

10/15/2019

1

237.2

0

0

237.16

Claim 2

12/22/2018

Installation

Paid

Serial 2

SA2

Cust2

12/13/2018

12/13/2018

1

216.6

0

20.49

237.12

Claim 3

5/17/2019

Warranty

Paid

Serial 2

SA2

Cust2

5/14/2019

5/14/2019

1

250

0

0

250

Claim 4

8/22/2019

Warranty

Paid

Serial 2

SA2

Cust2

8/16/2019

8/16/2019

1

286.2

47.56

4.76

338.48

Claim 5

9/1/2019

Installation

Paid

Serial 3

SA5

Cust3

8/16/2019

8/16/2019

1

629.2

510

51

1190.12

Claim 6

10/1/2019

Warranty

Paid

Serial 3

SA5

Cust3

10/1/2019

10/1/2019

1

100

200

50

350

Here is sample TransactionData:

BRAND	Invoice Date	Sales #	Model #	ITEM_DESC	Qty	SERIAL_NBR
WIDGIT	5/20/2019	S123	Model 1	Unit XYZ	1	Serial 1
WIDGIT	11/29/2017	S123	Model 2	Unit XYZ	1	Serial 1
WIDGIT	11/29/2017	S125	Model 3	Unit XYZ	1	Serial 2
WIDGIT	9/20/2019	S125	Model 4	Unit XYZ	1	Serial 2
WIDGIT	9/20/2019	S127	Model 5	Unit XYZ	1	Serial 3
WIDGIT	10/15/2019	S127	Model 6	Unit XYZ	1	Serial 4

Thank you