Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have slowly been learning Power BI and this issue I am working on has plagued me for some time now. These are reports that are being pulled a weekly basis for visibility inside my Firm.
My data layout is as follows:
Table 1 contains information about people, their Employers, and some other dimensional data.
Table 2 contains relational information regarding those people - specifically, who they know in our Firm.
Table 3 contains relational information regarding those Employers - specifically, who has a relationship with the Employers at our Firm.
Tables 2 and 3 are exported from their sources in such a way that for each relationship, there is a new row. Examples:
Table 2
Full Name | Employer | Firm Relationshp*
John Smith | Acme Corporation | Mr. CEO
John Smith | Acme Corporation | Other Staff Member
Table 3
Employer Name | Firm Relationship*
Acme Corporation | Ms. CFO
Acme Corporation | Ms. CFO's secretary
*A relationship can exist between an individual, but not their employer, and vice versa.
Currently, the data model is setup so there is a 1 to Many relationship between Table 1 and Table 2 via email addresses. Table 1 and Table 3 cannot be setup with a relationship because there is usually a Many to Many relationship (criteria from the data pull for Table 1 can return multiple individuals from the same Employer).
-----------------------------------------------------------------------------------------------------
GOAL: To have the final column (Connected To) show the relational information from Table 3, if it exists, otherwise Table 2.
If any additional information is needed, please ask. Any and all help is appreciated.
Thanks!
Edit for Clarification:
Given a [Full Name] from Table 1, if Table 3 [Connection] contains data for [Employer], return [Connection] Values from Table 3 where Table3[Employer Name] = Table1[Employer]. If Table 3 does not contain data for [Employer], return [Connection] Values from Table 2 where Table2[Full Name] = Table1[Full Name].
The values will be arranged on a matrix. I have an short sample data set to work with (I apologize it is not longer, I have no experience creating sample data).
In my example, the ideal output would be:
Full Name | Employer | Connection
Adam Fisher | Salmon Emporium | Brian Null
| Jeff Robinson
Amy Smith | Acme Corp | William Zheng
Bill Hanson | Hanson Windows | John Miller
| Jody Bank
Bill Weber | Nancy's Daycare | Sue Pillow
Branden King | Hanson Windows | John Miller
Felicia Day | Amazon | Dan Nuss
George Foreman| We Sell Grills | Don Ham
| Howard Donn
Larry Vance | Salmon Emporium |Brian Null
| Jeff Robinson
Nancy Weber | Nancy's Daycare | Sue Pillow
Troy Barnes | Barnes & Noble | (blank)
Link to sample data: https://drive.google.com/open?id=0B59iuV1_JBMUVFI1d3JRYlJRb0k
Solved! Go to Solution.
Hi @jwbtkd3,
I've modified your PBIX file and here is the output.
The idea is to Merge the three tables together using the Query Editor.
The table you are looking for is the one called Final
Hi @jwbtkd3,
Regarding "To have the final column (Connected To) show the relational information from Table 3, if it exists, otherwise Table 2.", do you mean if related [Connected To] doesn't exist in Table3, you want to return Table 2, right? Can you elaborate the requirement. It would be better if you can create a .pbix file with sample data for our test.
Best Regards,
Qiuyun Yu
Hello,
I can elaborate on the requirement, yes. Given a [Full Name] from Table 1, if Table 3 [Connection] contains data for [Employer], return [Connection] Values from Table 3 where Table3[Employer Name] = Table1[Employer]. If Table 3 does not contain data for [Employer], return [Connection] Values from Table 2 where Table2[Full Name] = Table1[Full Name].
The values will be arranged on a matrix. I have an short sample data set to work with (I apologize it is not longer, I have no experience creating sample data).
In my example, the ideal output would be:
Full Name | Employer | Connection
Adam Fisher | Salmon Emporium | Brian Null
| Jeff Robinson
Amy Smith | Acme Corp | William Zheng
Bill Hanson | Hanson Windows | John Miller
| Jody Bank
Bill Weber | Nancy's Daycare | Sue Pillow
Branden King | Hanson Windows | John Miller
Felicia Day | Amazon | Dan Nuss
George Foreman| We Sell Grills | Don Ham
| Howard Donn
Larry Vance | Salmon Emporium |Brian Null
| Jeff Robinson
Nancy Weber | Nancy's Daycare | Sue Pillow
Troy Barnes | Barnes & Noble | (blank)
Link to sample data: https://drive.google.com/open?id=0B59iuV1_JBMUVFI1d3JRYlJRb0k
Original Post updated with this data.
Hi @jwbtkd3,
I've modified your PBIX file and here is the output.
The idea is to Merge the three tables together using the Query Editor.
The table you are looking for is the one called Final
This works like a charm. Awareness of conditional columns would have helped me greatly, but organizing the data was key, too. Thank you for your help 🙂
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |