Power BI Measure

[kenneth0596]

Hi all,

I serisously need your help! I have been stuck with this problem FOREVER. Here is what Im trying to do: 

If Project Type = Type 1 then check the Rate Card Name from Table 2 and the Rate Card Business Title from Table 3 and get the Billable Rate from Table 4. If Project Type = Type 2 then get the Cost Rate from Table 3. Lastly, multiply this rate by the actual hours from Table 1. 

I was able to add new columns to Table 1 and calculate the fee before my company switched to a different data source (direct query). I now must come up with a measure since direct query wont allow me to add new columns. 

Any help would be greatly appreciated thank you!

Table 1 (Semantic Model)

Employee

Job Title

Actual Hours

Project Name

Table 2 (SharePoint)

Project Name

Project Type (Type 1/Type 2)

Rate Card Name

Table 3 (SharePoint)

Employee Name

Rate Card Business Title

Cost Rate

Table 4 (SharePoint)

Rate Card Name

Business Title

Billable Rate

[Shravan133]

Step 1: Create Relationships

First, ensure that you have relationships established between your tables:

1. Table 1 (Semantic Model) and Table 2 (SharePoint): Join on Project Name.
2. Table 1 (Semantic Model) and Table 3 (SharePoint): Join on Employee.
3. Table 2 (SharePoint) and Table 4 (SharePoint): Join on Rate Card Name.
4. Table 3 (SharePoint) and Table 4 (SharePoint): Join on Rate Card Business Title to Business Title.

Step 2: Create a Calculated Column for the Rate

Next, create a calculated column in Table 1 to fetch the appropriate rate based on the project type.

Rate =
VAR ProjectType = RELATED('Table 2'[Project Type])
VAR RateCardName = RELATED('Table 2'[Rate Card Name])
VAR RateCardBusinessTitle = RELATED('Table 3'[Rate Card Business Title])
VAR BillableRate = LOOKUPVALUE('Table 4'[Billable Rate], 'Table 4'[Rate Card Name], RateCardName, 'Table 4'[Business Title], RateCardBusinessTitle)
VAR CostRate = RELATED('Table 3'[Cost Rate])
RETURN
IF(
ProjectType = "Type 1",
BillableRate,
IF(
ProjectType = "Type 2",
CostRate,
BLANK()
)
)

Step 3: Calculate the Total Cost

Now, create a measure to calculate the total cost by multiplying the rate by the actual hours.

TotalCost = SUMX( 'Table 1', 'Table 1'[Actual Hours] * 'Table 1'[Rate] )

[kenneth0596]

Related is not working because these two relationships are many to many. 

1. Table 2 (SharePoint) and Table 4 (SharePoint): Join on Rate Card Name.
2. Table 3 (SharePoint) and Table 4 (SharePoint): Join on Rate Card Business Title to Business Title.