Error showing Relationship between tables, although the tables are linked

[ashmitp869]

Hi,
I have DM like below with relationship joining table (Plan_DailyPlanHoursResourceVersion and BT) with DailyPlanId

Plan_DailyPlanHoursResourceVersion and BT

The requirement is to get the cost - that is 

Cost = (StraightTimeRate × Sum of Approved Std Hours) + (OverTimeRate × Sum of Approved OT Hours) + (DoubleTimeRate × Sum of Approved DT Hours)

I am unsure why my below dax having error with "The column 'BT[StraightTimeRate]' either doesn't exist or doesn't have a relationship to any table available in the current context."

Not sure why having a relationship error, even though it linked.

Cost = 
CALCULATE(
    SUMX(
        Plan_DailyPlanHoursResourceVersion,
        SWITCH(
            TRUE(),

            -- Equipment
            Plan_DailyPlanHoursResourceVersion[ResourceTypeDescription] = "Equipment",
                Plan_DailyPlanHoursResourceVersion[ApprovedStdHours] * RELATED(Core_ProjectEquipment[UnitCost]),

            -- Labor: Get rates from BT table via Plan_DailyPlanHoursResourceDetailVersion
            Plan_DailyPlanHoursResourceVersion[ResourceTypeDescription] = "Labor",
                Plan_DailyPlanHoursResourceVersion[ApprovedStdHours] * RELATED(BT[StraightTimeRate]) +
                Plan_DailyPlanHoursResourceVersion[ApprovedOTHours] * RELATED(BT[OverTimeRate]) +
                COALESCE(Plan_DailyPlanHoursResourceVersion[ApprovedDTHours], 0) * RELATED(BT[DoubleTimeRate])
        )
    ),
    Plan_DailyPlanHoursResourceDetailVersion[IsActive] = TRUE(),
    Plan_DailyPlanHoursResourceDetailVersion[IsLatest] = TRUE(),
    Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] = "Approved"
)

Plan_DailyPlanHoursResourceVersion

DailyPlanCostItemResourceId

DailyPlanId

ProjectId

PlanDate

PlanTitle

DailyPlanStatusId

DailyPlanStatusDescription

ResourceType

ResourceTypeDescription

ResourceId

CostItemId

TempTaskId

TaskDisplay

ApprovedStdHours

ApprovedOTHours

ApprovedDTHours

DPEKey

3096

323

74

1/04/2025

EBA Tom Kelly 1

4

Approved

3003

Labor

2635

11327

1005

8

2

2

74-2635

 
BT

DailyPlanId	DefaultProjectRateCodeId	ProjectRateCodeId	ProjectRateCodeName	ProjectId	StraightTimeRate	OverTimeRate	DoubleTimeRate
323	65	65	OP1UFLA	74	100.58	150.5683	150.56826

[v-pagayam-msft]

Hi @ashmitp869 ,
I wanted to check and see if you had a chance to review our previous message or Please let me know if everything is sorted or if you need any further assistance.
if it helps,consider accepting it as solution.

[v-pagayam-msft]

Hi @ashmitp869 ,

Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

[v-pagayam-msft]

Hi @ashmitp869 ,
Following up to check whether you got a chance to review the suggestion given.If it helps,consider accepting it as solution,it will be helpful for other members of the community who have similar problems as yours to solve it faster. Glad to help.
Thank you.

[v-pagayam-msft · Accepted solution]

Hi @ashmitp869 ,
I tried to recreate it on my end .So that,I followed below steps:

First,create the relationships between the table as below:

• Between Plan_DailyPlanHoursResourceVersion[DailyPlanId] and BT[DailyPlanId]
• Between Plan_DailyPlanHoursResourceVersion[ResourceId] and Core_ProjectEquipment[ResourceId] (1-to-many).
• Between Plan_DailyPlanHoursResourceVersion[DailyPlanCostItemResourceId] and Plan_DailyPlanHoursResourceDetailVersion[DailyPlanCostItemResourceId] (1-to-1 or 1-to-many, depending on your data).

Then, Use the below measure:

Cost =

CALCULATE(

    SUMX(

        Plan_DailyPlanHoursResourceVersion,

        SWITCH(

            TRUE(),

            -- Equipment

            Plan_DailyPlanHoursResourceVersion[ResourceTypeDescription] = "Equipment",

                Plan_DailyPlanHoursResourceVersion[ApprovedStdHours] * RELATED('Core_ProjectEquipment (for Equipment Cost)'[UnitCost]),

            -- Labor: Get rates from BT table using LOOKUPVALUE

            Plan_DailyPlanHoursResourceVersion[ResourceTypeDescription] = "Labor",

                Plan_DailyPlanHoursResourceVersion[ApprovedStdHours] * 

                    COALESCE(

                        LOOKUPVALUE(

                            BT[StraightTimeRate], 

                            BT[DailyPlanId], Plan_DailyPlanHoursResourceVersion[DailyPlanId]

                        ),

                        0

                    ) +

                Plan_DailyPlanHoursResourceVersion[ApprovedOTHours] * 

                    COALESCE(

                        LOOKUPVALUE(

                            BT[OverTimeRate], 

                            BT[DailyPlanId], Plan_DailyPlanHoursResourceVersion[DailyPlanId]

                        ),

                        0

                    ) +

                COALESCE(Plan_DailyPlanHoursResourceVersion[ApprovedDTHours], 0) * 

                    COALESCE(

                        LOOKUPVALUE(

                            BT[DoubleTimeRate], 

                            BT[DailyPlanId], Plan_DailyPlanHoursResourceVersion[DailyPlanId]

                        ),

                        0

                    )

        )

    ),

    Plan_DailyPlanHoursResourceDetailVersion[IsActive] = TRUE(),

    Plan_DailyPlanHoursResourceDetailVersion[IsLatest] = TRUE(),

    Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] = "Approved"

)

Please refer the screenshot and the file for your reference:

If this answer meets your requirements,consider accepting it as solution.

Regards,
Pallavi.

[v-pagayam-msft]

Hi @ashmitp869 ,
Thank you @bhanu_gautam for the helpful response!

The approach shared by the Bhanu is a good workaround because it doesn’t depend on relationship direction and still gets the correct rates.
The error happens because RELATED only works when the table you are pulling from (BT) is on the one side of a one-to-many relationship, and BT[DailyPlanId] must be unique. If there are multiple rows with the same DailyPlanId or the relationship is not set up correctly, RELATED will not work. 

Hope this helps.If so,consider accepting it as solution.

Thank you.

Regards,
Pallavi.

[bhanu_gautam]

@ashmitp869 , Try using

dax
Cost =
CALCULATE(
SUMX(
Plan_DailyPlanHoursResourceVersion,
SWITCH(
TRUE(),
-- Equipment
Plan_DailyPlanHoursResourceVersion[ResourceTypeDescription] = "Equipment",
Plan_DailyPlanHoursResourceVersion[ApprovedStdHours] * RELATED(Core_ProjectEquipment[UnitCost]),

-- Labor: Get rates from BT table via Plan_DailyPlanHoursResourceDetailVersion
Plan_DailyPlanHoursResourceVersion[ResourceTypeDescription] = "Labor",
Plan_DailyPlanHoursResourceVersion[ApprovedStdHours] * RELATED(BT[StraightTimeRate]) +
Plan_DailyPlanHoursResourceVersion[ApprovedOTHours] * RELATED(BT[OverTimeRate]) +
COALESCE(Plan_DailyPlanHoursResourceVersion[ApprovedDTHours], 0) * RELATED(BT[DoubleTimeRate])
)
),
Plan_DailyPlanHoursResourceDetailVersion[IsActive] = TRUE(),
Plan_DailyPlanHoursResourceDetailVersion[IsLatest] = TRUE(),
Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] = "Approved"
)

If this still gives an error try using 

LOOKUPVALUE as an alternative to RELATED to fetch the rates from the BT table:

[ashmitp869]

Hi @bhanu_gautam @v-pagayam-msft 
Can you help with the sample file - I have made some changes in the DM.

Will you please able to have a look.
https://github.com/suvechha/samplepbi/blob/main/LabourReport.pbix

The expected result is Cost Calculation- 
Cost = (StraightTimeRate × Sum of Approved Std Hours) + (OverTimeRate × Sum of Approved OT Hours) + (DoubleTimeRate × Sum of Approved DT Hours)
Thanks