Forum Discussion

FelipMark's avatar
FelipMark
Icon for Helper II rankHelper II
3 years ago

I can't relate 2 fact tables

Hi guys! I want to understand which customers went from one table to another. For this I have two tables with the phone number in common, but in one of the tables the number can be in 3 different columns.

To solve this I tried two methods without success.

1. I created a dimension table listing the cell phone numbers of the 4 columns and removing the duplicates, I created an index column and brought the customer's name from the other table. But it happened that a customer who has 2 phone numbers generated 2 different codes.

 

2. I took table 2 (which contains 3 columns with cell phone numbers) unpivoted the ID, Phone 1, Phone 2, Phone 3, eliminated duplicates, after that i left merged and got the ID of table 1. So I have 3 columns - table 1 ID, phone, Table 2 ID. But when I go to relate the tables an ambiguity error appears.

 

I've been trying this for days, if anyone can help me I'd be very grateful.

Need Help
Show and Tell
Tutorial Requests

1 Reply

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, FelipMark ;

     please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. 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.

    Solved: How to upload PBI in Community - Microsoft Power BI Community


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