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 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
Need Help

2 Replies

  • 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.