Trying to Create a Calculated Column by joining mu...

htw17 · ‎11-02-2016

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.

v-ljerr-msft · ‎11-03-2016

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.

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

htw17 · ‎11-05-2016

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.

v-ljerr-msft · ‎11-06-2016

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.

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