Forum Discussion

kenneth0596's avatar
kenneth0596
Frequent Visitor
2 years ago

Power BI Measure

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 Ra...
Need Help
Shravan133's avatar
Shravan133
Super User
2 years ago

 

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's avatar
kenneth0596
Frequent Visitor
2 years ago

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.