Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
htw17
New Member

Trying to Create a Calculated Column by joining multiple tables

I am new to Power BI. I went through most of the tutorials but I am not seeing a simple solution to my issue which I would assume is an easy one for Power BI. I am connecting to my Salesforce Data. I need to calculate Total Professional Fees.

 

This is derived by connecting 6 tables together.

An Employee has a Job Function Associated with them which provides a Standard Bill Rate (e.g. Jon Smith, Consultant =100)

From the Employee table you get the Employee Id / Name and the Job Function

The Job Function Table provides the Bill Rate for that Job Function

 

We then have a set of timesheets where the Employees time for an Engagement is provided

For instance Jon Smith Works 50 hours on Engagement ABC

 

The final Table I am trying to create in Power BI is something like this

 

For the Month of October 2016

Employee       Total Hours worked   Employee Bill Rate        Employee Fees

Employee A      100                              100                            $10,000

Employee B       200                              150                            $30,000

 

I am able to setup the relationship between the various tables in the relationship view but when I drag the fields into the Visualization the fields that need to use the joined relationships are not working correctly so the sums are not working correctly. For instance every employee shows the same Bill rate even though they have different job functions.

 

I asume Power BI has a simple way to relate the tables through a query but I must be missing how that is done.

4 REPLIES 4
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @htw17,


I asume Power BI has a simple way to relate the tables through a query but I must be missing how that is done.


You should be able to use RELATED or RELATEDTABLE functions (DAX) to relate tables with relationships when creating the calculate column in this scenario. Smiley Happy

 

In addition, please make sure you are using the star schema for your data pattern when creating multiple relationships between your tables. As cross filtering can create an ambiguous set of relationships in data pattern with loops. 

 

Reference:

Create and manage relationships in Power BI Desktop and A similar thread

 

Regards

JerriLi,

Thank you for your help the link you sent on creating and managing relationships was very useful. It is true that the setup of my tables is not a star schema. I have tried several different approaches to try to resolve this issue. I tried to create a new table from my existing tables to try to simplify the relationships. I am having trouble understanding how to accomplish this in Power BI. It seems creating a table is meant to create tables with DAX but does not seem to be what I need.

 

In SQL Server I can write a stored procedure that creates Temp tables that then allows me to run queries against those temp tables with my core tables and will create my final data set that I need for my report. I guess my main question is how do I accomplish this in Power BI?

 

The link you sent seems to suggest if your relationships are not star schema that you can bring in tables more than once to address this issue. I am afraid I am getting lost in trying many different ways to try to get the data set up in a way that works in PowerBI.

 

What I am looking for is direction on where and how I can alter my tables for Power BI. Since my data is coming from Salesforce I really want to be able to transform the data in Power BI rather than in Salesforce.

Hi @htw17,


What I am looking for is direction on where and how I can alter my tables for Power BI. Since my data is coming from Salesforce I really want to be able to transform the data in Power BI rather than in Salesforce.


You can do it within Query Editor in Power BI Desktop. The Query Editor UI is used to transform data from multiple sources and various data types to shape business data to facilitate loading into the Power BI Desktop data model.

 

editorQuery.PNG

 

Reference:

Shape and combine data in Power BI Desktop

5 Minutes To Insight: Monitor Your Salesforce Data With Power BI

Power Query (informally known as "M") Formula reference

 

Regards

htw17
New Member

Help11-2-2016 5-53-54 PM.jpg

 

Here is a view of my tables

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors