So I have two datasets I'm attempting to merge. Both from different sources but both contain the same Company ID's highlighted in each table:
I'm unable to connect the two because my CService dataset contains duplicate co values because a company can have more than one service:
I've already tried creating a calculated table to get the distinct values, but it does not give me everything I need. I need to build some formulas but I do not know how to get around this without establishing a direct connection between these two datasets. I have retention formulas already established in my CService dataset based on the start and end dates, and I just need the account manager field from my EMS Survey dataset to add the retention formulas to see what the retention is by account manager based on the company ID my datasets in common. Any ideas??
@Anonymous as you already create a calculated table with distinct value, I assume at that point you are able to connect both the tables with this new calculated table? Are you having trouble setting relationship? What problem we are solving here?
@parry2k yes, that is my exact issue. Even though I've created a table of distinct co values from 'CService', I still cannot create a table or any other visualization that has fields from 'CService' and fields from 'EMS Survey'. It only understands the relationship between 'Cservice'<->'Distinct Co' and 'EMS Survey'<->'Distinct Co' and I'm trying to get to a point where I have 'CService'<->'EMS Survey' so I can use the necessary fields from each. I thought my calcuated table of the distinct co values would bridge the gap between these two datatsets.
Can you share the sample dataset? I don't see any challenge once relationship are created. My assumption is at this point your both tables are connected to this distinct table you created, make sure your relatonship cross filter direction is set to both, which you can check on relationship dialog box.
If you take a simple table visual, drop fields from both these two fields, I expect you will get correct result.
@Ashish_Mathur Here are my dataset samples:
As you can see in Table 1, a company ID can be duplicated due to multiple services and start and end dates, so I cannot create a relationship based on that column. This is the result I'm looking to get, which I've just created manually:
This will be a conference that you do not want to miss!
Don't miss the Power BI Dev Camp this week!
Check out a full recap of the month!
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.