Power BI Webinar Part 3 - Relating the data

Creating relationships in your Power BI report involves linking data from QorusDocs, SharePoint, and Salesforce. This process is crucial if you want to be able to report on the full proposal process from content creation to utilization in a proposal for a prospect in your CRM.

Although we have focused on Salesforce for this example, you can follow similar steps in relating your CRM data or data from another system to your QorusDocs data in Power BI. For those using Salesforce, it is good to also point out that you are not limited to using just the "opportunity" Salesforce Object; you can in fact use any relevant object or data point. The key is understanding what basis to use for creating these relationships, ensuring your data is interconnected and your analysis insightful.

1. Managing Relationships

Managing Relationships 

In the Webinar, you can find this section at 23:03

To kick things off, you will need to create relationships between all your data so that you can interact with it. To achieve this, follow the steps laid out below:

We begin by opening the “Manage Relationships” work area.

1. Navigate to “Modeling”
2. Select “Manage Relationships” in the top ribbon
3. If there are any existing relationships found in the window that pops up, please delete them. These are automatically created by Power BI. We will be creating our own. Please note that the relationships shown in the screenshot below are in fact the relationships we will be creating.
4. Once you’ve cleared the existing relationships, select “New” so you can define the relationships we’ll need for this report.

The first relationship we will create is between the “All Libraries Combined” and “Customer Activities” tables:

1. Select the “All Libraries Combined” table – if you missed this part, click here.
2. Scroll across to find and choose the “FieldValuesAsTextUniqueid” column.
3. Next, select the “Customer Activities” table.
4. Scroll to find and choose “Fieldid”.
5. Choose a “Many to many(*;*)” relationship (cardinality).
6. Check the “Make this relationship active” box.
7. The cross-filter direction must be “Both”.
8. Click “OK” once complete.

Next, we will relate the “Customer Activities” table to the “Content Used in Opportunities” table.

1. Select the “Customer Activities” table.
2. Use the “FieldID” column.
3. Select the “Content Used in Opportunities” table.
4. Use the “FieldID” column here, too.
5. Set the Cardinality to “Many to Many”.
6. Check the “Make this relationship active” box.
7. Set cross-filter direction to “Both”.
8. Click “OK” once complete.

Next up are the “Customer Activities” and “Content Used in Pursuits” tables:

1. Select the “Customer Activities” table.
2. Use the “FieldID” column.
3. Select the “Content Used in Pursuits” table.
4. Use the “FieldID” column here, too.
5. Set the Cardinality to “Many to Many”.
6. Check the “Make this relationship active” box.
7. Set cross filter direction to “Both”.
8. Click “OK” once complete.

Next, we have the “Customer Activities” to “SharedDocuments” tables:

1. Select the “Customer Activities” table.
2. Use the “FieldID” column.
3. Select the “SharedDocuments” table.
4. Use the “FieldID” column here, too.
5. Set the Cardinality to “Many to Many”.
6. Check the “Make this relationship active” box.
7. Set cross filter direction to “Both”.
8. Click “OK” once complete.

Then, there are the “Customer Activities” and “Pursuits” tables (please note the change in the Unique Identifier names here at points 2 and 4!):

1. Select the “Customer Activities” table.
2. Use the “PursuitID” column.
3. Select the “Pursuits” table.
4. Use the “ID” column here.
5. Set the Cardinality to “Many to Many”.
6. Check the “Make this relationship active” box.
7. Set cross filter direction to “Both”.
8. Click “OK” once complete.

Next, we need to relate the “Customer Activities” table to the “UserInfo” table – (please note the change in the Cardinality at point 5!):

1. Select the “Customer Activities” table.
2. Use the “UserID” column.
3. Select the “UserInfo” table.
4. Use the “ID” column here.
5. Set the Cardinality to “Many to one (*;1)”.
6. Check the “Make this relationship active” box.
7. Set cross-filter direction to “Both”.
8. Click “OK” once complete.

Then, we will relate the“Pursuits” to “PursuitSmartFields” tables – (again, please note the change in the Cardinality at point 5!):

1. Select the “Pursuits” table.
2. Use the “ID” column.
3. Select the “PursuitSmartFields” table.
4. Use the “PursuitID” column here.
5. Set the Cardinality to “One to one (1;1)”.
6. Check the “Make this relationship active” box.
7. Set cross filter direction to “Both”.
8. Click “OK” once complete.

Next, the “PursuitSmartFields” and “Opportunity” tables:

1. Select the “PursuitSmartFields” table.
2. Use the “OpportunityID” column.
3. Select the “Opportunity” table.
4. Use the “ID” column here.
5. Set the Cardinality to “Many to Many”.
6. Check the “Make this relationship active” box.
7. Set cross filter direction to “Both”.
8. Click “OK” once complete.

Then, the “ShareDocumentViews” to “SharedDocuments” tables:

1. Select the “ShareDocumentViews” table.
2. Use the “SharedID” column.
3. Select the “SharedDocuments” table.
4. Use the “ID” column here.
5. Set the Cardinality to “Many to one (*;1)”.
6. Check the “Make this relationship active” box.
7. Set cross filter direction to “Both”.
8. Click “OK” once complete.

And the last relationship to create is between the “UserInfo” and  “User Information List” tables:

1. Select the “UserInfo” table.
2. Use the “EmailAddress” column.
3. Select the “User Information List” table.
4. Use the “EMail” column here.
5. Set the Cardinality to “Many to Many”.
6. Check the “Make this relationship active” box.
7. Set cross filter direction to “Both”.
8. Click “OK” once complete.

And there you have it. All your data is now connected to Power BI, been transformed so that it can be more easily manipulated and digested, and related so we can get some really powerful insights into your work bid response and proposal management processes.

Next is Part 4 - Calculations and formulas. This will cover the formulas we’ll need to drive the interactive report visuals.

Back to the top