Hi.
I am certainly missing something concerning the RelationShip between different tables. Trying to get something and don't understand what is not correct.
In the attached file, there are 3 tables. Lead, Ramplus and ID. I created the ID (from both other tables apend and remove duplicate) in order to get a connection between the 2 other tables. I need to analyze data from Ramplus according to performances of the ID in the Lead table. i.e. : Return the count of ID in Ramplus from each campain in Lead.
I am receiving I need to create relationship while I created it (received in Sheet1) ... What am I doing wrong?
Many thanks, Bruno.
p.s. Some of the data is in hebrew, it is of non interest, just counting the line and the titles are all in english.
Hi Bruno,
Please watch lesson 6.07 of the Power Pivot course which explains how to relate multiple fact tables and which fields must be used in the row and column labels of the PivotTable.
That said, if you want to count ID by campaign then you need campaign in your Ramplus table and in the dimension table. Then you can create relationships to the campaign field in each fact table from the dimension table.
Hope that points you in the right direction.
Mynda
Mynda, thanks for that. I never had the opportunity to thanks you for your course and site, You and your team are amazing!
In fact, I did inserted a Dim table in order to get all the ID from both fact tables, (I was a good student ..) but I didn't inserted a campaign since there is no campaign data in the Ramplus table, this is exactly what I need to connect.
I have 2 different lists from 2 different sources : Ramplus and Leads. Ramplus is a membership list with many purchases once they are converted from lead to membership. Lead is a list of potential names we get from campaigns we pay for. Some appear in the Ramplus list later on, others are not.
I wanted to connect the two lists with the ID which can be a DIM table for that, and though I would be able to get all the fields from both tables connected together by the ID list. Analyzing the Ramplus data for the ID that appear in the Lead data. I understand it is not an option here (it is working in SQL if I remember correctly?)
The only solution I have is to merge both lists thru the ID ?
Hi Bruno,
It's great to hear you're enjoying our site and content! Thanks so much
Thanks for explaining the data in more detail. It helps understand the objective. The issue with your data is that the Leads table contains duplicate IDs and therefore multiple campaigns for the same ID. Therefore, how does Excel/Power Query know which campaign relates to which membership in the Ramplus table?
Mynda
I didn't pay attention to that. In fact, I created these tables by my own since I know I will have to deal with a very similar model in the close future (project I am working on to get the contract). I didn't see the real table yet since I am working with the company to create it from the different sources they are running the campaigns.
Assuming I have one ID line with a given and unique campaign in the lead table, but the same ID may have several lines in the Ramplus table (one source of client that buy several products), the better and possible solution should be the ID Dim table I created I understand ?
My goal is to calculate the benefit I receive from each campaign, or in other words, the ROI of these campaigns : I spend $1000 for campaign A, receive 50 leads, 25 are converted and get an ID and these ID spent $x
Hi Bruno,
If the final leads table contains unique IDs then it can have a direct relationship with the Ramplus table allowing you to filter Ramplus by campaign. In other words, the leads table will act as a dimension table.
Mynda
