Help with the dax Formula- ApprovedStdCost

[ashmitp869]

Hi All,

I have a requirement like below.
Please help me with the dax formula.

For Project NOT EQUAL 1 then keep the same logic of ApprovedStdCost
Else
When ProjectRateCodeDescription is NULL OR BLANK

Get The StarightTimeRate from the table Core_ProjectRateCode with FY25.

Expected Result

dax formula 

ApprovedStdCost = 
CALCULATE(
    SUMX(
        Plan_DailyPlanHoursResourceVersion,
        VAR StdHours = Plan_DailyPlanHoursResourceVersion[ApprovedStdHours]
        VAR ResourceType = Plan_DailyPlanHoursResourceVersion[ResourceType]

        -- Equipment rate
        VAR EquipRate = RELATED(Core_ProjectEquipment[UnitCost])

        -- Related BT Key to pull Craft or RateCodeId
        VAR BTKey = Plan_DailyPlanHoursResourceVersion[Key]

        -- Pull Default RateCodeId and Craft from BT
        VAR DefaultRateCodeId = LOOKUPVALUE(BT[DefaultProjectRateCodeId], BT[Key], BTKey)
        VAR CraftDesc         = LOOKUPVALUE(BT[Craft], BT[Key], BTKey)

        -- Build dynamic rate code description (e.g., "Pipefitter BASE")
        VAR MatchRateCodeDesc = CraftDesc & " BASE"

        -- Filter Core_ProjectRateCode table based on dynamic description
        VAR RateCodeTable =
            FILTER(
                ALL(Core_ProjectRateCode),
                Core_ProjectRateCode[ProjectRateCodeDescription] = MatchRateCodeDesc
            )

        -- Get rate from Core_ProjectRateCode if DefaultRateCodeId is present
        VAR StdRate_RateCode = RELATED(Core_ProjectRateCode[StraightTimeRate])

        -- Get rate from Craft-based match if no RateCodeId
        VAR StdRate_Craft = MAXX(RateCodeTable, Core_ProjectRateCode[StraightTimeRate])

        -- Fallback: Employee StraightTimeRate
        VAR StdRate_Emp = RELATED(Core_ProjectEmployee[StraightTimeRate])

        -- Final resolved StdRate
        VAR StdRate = IF(
            NOT ISBLANK(DefaultRateCodeId),
            StdRate_RateCode,
            COALESCE(StdRate_Craft, StdRate_Emp, 0)
        )

        RETURN
            SWITCH(
                TRUE(),
                ResourceType = "Equipment", StdHours * EquipRate,
                ResourceType = "Labor",    StdHours * StdRate,
                0
            )
    ),
    Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] = "Approved"
)

sample file attached.
https://github.com/suvechha/samplepbi/blob/main/samplelabourcost.pbix

[v-hjannapu]

Hi @ashmitp869,

I would also take a moment to thank @GrowthNatives  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @ashmitp869,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[GrowthNatives]

Hi @ashmitp869 ,
You would need to change your DAX code to get the desired result 

(If your FY column is numeric like 2025, change the "FY25" filter accordingly.)

DAX
ApprovedStdCost :=
CALCULATE (
    SUMX (
        Plan_DailyPlanHoursResourceVersion,
        VAR StdHours     = Plan_DailyPlanHoursResourceVersion[ApprovedStdHours]
        VAR ResourceType = Plan_DailyPlanHoursResourceVersion[ResourceType]
        VAR ProjectId    = Plan_DailyPlanHoursResourceVersion[ProjectId]

        -- Equipment rate (as-is)
        VAR EquipRate = RELATED ( Core_ProjectEquipment[UnitCost] )

        -- From BT (as-is)
        VAR BTKey              = Plan_DailyPlanHoursResourceVersion[Key]
        VAR DefaultRateCodeId  = LOOKUPVALUE ( BT[DefaultProjectRateCodeId], BT[Key], BTKey )
        VAR CraftDesc          = LOOKUPVALUE ( BT[Craft], BT[Key], BTKey )
        VAR MatchRateCodeDesc  = CraftDesc & " BASE"

        -- Row’s joined description (may be blank)
        VAR PRCD = RELATED ( Core_ProjectRateCode[ProjectRateCodeDescription] )

        -- Normal resolution (your existing paths)
        VAR StdRate_RateCode :=
            CALCULATE (
                MAX ( Core_ProjectRateCode[StraightTimeRate] ),
                KEEPFILTERS ( Core_ProjectRateCode[ProjectRateCodeId] = DefaultRateCodeId )
            )

        VAR StdRate_Craft :=
            CALCULATE (
                MAX ( Core_ProjectRateCode[StraightTimeRate] ),
                KEEPFILTERS ( Core_ProjectRateCode[ProjectRateCodeDescription] = MatchRateCodeDesc )
            )

        VAR StdRate_Emp = RELATED ( Core_ProjectEmployee[StraightTimeRate] )

        -- *** Special override: Project = 1 AND PRCD blank -> force FY25 rate ***
        VAR StdRate_FY25_Override :=
            IF (
                ProjectId = 1 && ( ISBLANK ( PRCD ) || PRCD = "" ),
                CALCULATE (
                    MAX ( Core_ProjectRateCode[StraightTimeRate] ),
                    REMOVEFILTERS ( Core_ProjectRateCode ),
                    -- Prefer explicit RateCodeId if we have it; else match by craft "BASE"
                    KEEPFILTERS (
                        IF (
                            NOT ISBLANK ( DefaultRateCodeId ),
                            Core_ProjectRateCode[ProjectRateCodeId] = DefaultRateCodeId,
                            Core_ProjectRateCode[ProjectRateCodeDescription] = MatchRateCodeDesc
                        )
                    ),
                    Core_ProjectRateCode[FiscalYear] = "FY25"   -- change to 2025 if numeric
                )
            )

        -- Final resolved straight time rate
        VAR StdRate :=
            COALESCE (
                StdRate_FY25_Override,                   -- override first when applicable
                IF ( NOT ISBLANK ( DefaultRateCodeId ), StdRate_RateCode ),
                StdRate_Craft,
                StdRate_Emp,
                0
            )

        RETURN
            SWITCH (
                TRUE (),
                ResourceType = "Equipment", StdHours * EquipRate,
                ResourceType = "Labor",     StdHours * StdRate,
                0
            )
    ),
    Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] = "Approved"
)

Things to be noted : 
1. FiscalYear type: If Core_ProjectRateCode[FiscalYear] is numeric (2025), replace "FY25" with 2025.
2. Relationships: This assumes Plan_DailyPlanHoursResourceVersion → Core_ProjectRateCode and → Core_ProjectEmployee relationships are active so RELATED() works.
3. Craft “BASE” rows: Ensure rows like "Delivery Manager BASE" exist in Core_ProjectRateCode for FY25; otherwise the fallback becomes the employee rate.
4. If you need the override to consider effective dates instead of FiscalYear, swap in date filters inside the CALCULATE() that builds StdRate_FY25_Override.

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]

[ashmitp869]

Hi ,
I have implemented this dax

ApprovedStdCostDynamic_DateBased = 

CALCULATE (
    SUMX (
        Plan_DailyPlanHoursResourceVersion,
        VAR StdHours     = Plan_DailyPlanHoursResourceVersion[ApprovedStdHours]
        VAR ResourceType = Plan_DailyPlanHoursResourceVersion[ResourceType]
        VAR ProjectId    = Plan_DailyPlanHoursResourceVersion[ProjectId]

        -- Equipment rate (as-is)
        VAR EquipRate = RELATED ( Core_ProjectEquipment[UnitCost] )

        -- From BT (as-is)
        VAR BTKey              = Plan_DailyPlanHoursResourceVersion[Key]
        VAR DefaultRateCodeId  = LOOKUPVALUE ( BT[DefaultProjectRateCodeId], BT[Key], BTKey )
        VAR CraftDesc          = LOOKUPVALUE ( BT[Craft], BT[Key], BTKey )
        VAR MatchRateCodeDesc  = CraftDesc & " BASE"

        -- Row’s joined description (may be blank)
       -- VAR PRCD = RELATED ( BT[DefaultProjectRateCodeId] )
        VAR PRCD    = Plan_DailyPlanHoursResourceVersion[ProjectRateCodeDescription]

        -- Normal resolution (your existing paths)
        VAR StdRate_RateCode =
            CALCULATE (
                MAX ( Core_ProjectRateCode[StraightTimeRate] ),
                KEEPFILTERS ( Core_ProjectRateCode[ProjectRateCodeId] = DefaultRateCodeId )
            )

        VAR StdRate_Craft =
            CALCULATE (
                MAX ( Core_ProjectRateCode[StraightTimeRate] ),
                KEEPFILTERS ( Core_ProjectRateCode[ProjectRateCodeDescription] = MatchRateCodeDesc )
            )

        VAR StdRate_Emp = RELATED ( Core_ProjectEmployee[StraightTimeRate] )

        -- *** Special override: Project = 1 AND PRCD blank -> force FY25 rate ***
        VAR StdRate_FY25_Override =
            IF (
                ProjectId = 1 && ( ISBLANK ( PRCD ) || PRCD = "" ),
                CALCULATE (
                    MAX ( Core_ProjectRateCode[StraightTimeRate] ),
                    REMOVEFILTERS ( Core_ProjectRateCode ),
                    -- Prefer explicit RateCodeId if we have it; else match by craft "BASE"
                    KEEPFILTERS (
                        IF (
                            NOT ISBLANK ( DefaultRateCodeId ),
                            Core_ProjectRateCode[ProjectRateCodeId] = DefaultRateCodeId,
                            Core_ProjectRateCode[ProjectRateCodeDescription] = MatchRateCodeDesc
                        )
                    ),
                    Core_ProjectRateCode[FY] = 2025   -- change to 2025 if numeric
                )
            )

        -- Final resolved straight time rate
        VAR StdRate =
            COALESCE (
                StdRate_FY25_Override,                   -- override first when applicable
                IF ( NOT ISBLANK ( DefaultRateCodeId ), StdRate_RateCode ),
                StdRate_Craft,
                StdRate_Emp,
                0
            )

        RETURN
            SWITCH (
                TRUE (),
                ResourceType = "Equipment", StdHours * EquipRate,
                ResourceType = "Labor",     StdHours * StdRate,
                0
            )
    ),
    Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] = "Approved"
)

But not getting the result as expected.
Will you kindly have a look on the sample file attached.
https://github.com/suvechha/samplepbi/blob/main/samplelabourcost.pbix

@danextian Please have a look - if you can help.

[GrowthNatives · Accepted solution]

Hi @ashmitp869 , 
You can try these steps to get the result 
Detects “Project = 1” whether Project is numeric or text like “1 A”.
Applies the FY=2025 override only when Project=1 and ProjectRateCodeDescription is blank.

Uses TREATAS for stable, row-level filters.

DAX
ApprovedStdCostDynamic_DateBased :=
VAR ApprovedRows =
    FILTER (
        Plan_DailyPlanHoursResourceVersion,
        Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] = "Approved"
    )
RETURN
SUMX (
    ApprovedRows,
    VAR StdHours      = Plan_DailyPlanHoursResourceVersion[ApprovedStdHours]
    VAR ResourceType  = Plan_DailyPlanHoursResourceVersion[ResourceType]
    VAR ProjectRaw    = Plan_DailyPlanHoursResourceVersion[ProjectId]

    /* Project = 1 check works for both numeric and text like "1 A" */
    VAR IsProject1 :=
        IF (
            ISNUMBER ( ProjectRaw ),
            ProjectRaw = 1,
            LEFT ( FORMAT ( ProjectRaw, "" ), 1 ) = "1"
        )

    /* Rates from related tables / BT lookups */
    VAR EquipRate          = RELATED ( Core_ProjectEquipment[UnitCost] )
    VAR BTKey              = Plan_DailyPlanHoursResourceVersion[Key]
    VAR DefaultRateCodeId  = LOOKUPVALUE ( BT[DefaultProjectRateCodeId], BT[Key], BTKey )
    VAR CraftDesc          = LOOKUPVALUE ( BT[Craft], BT[Key], BTKey )
    VAR MatchRateCodeDesc  = CraftDesc & " BASE"
    VAR PRCD               = Plan_DailyPlanHoursResourceVersion[ProjectRateCodeDescription]

    /* Normal resolution paths */
    VAR StdRate_RateCode :=
        CALCULATE (
            MAX ( Core_ProjectRateCode[StraightTimeRate] ),
            TREATAS ( { DefaultRateCodeId }, Core_ProjectRateCode[ProjectRateCodeId] )
        )

    VAR StdRate_Craft :=
        CALCULATE (
            MAX ( Core_ProjectRateCode[StraightTimeRate] ),
            TREATAS ( { MatchRateCodeDesc }, Core_ProjectRateCode[ProjectRateCodeDescription] )
        )

    VAR StdRate_Emp = RELATED ( Core_ProjectEmployee[StraightTimeRate] )

    /* Build a reusable filter for the override */
    VAR OverrideFilter :=
        IF (
            NOT ISBLANK ( DefaultRateCodeId ),
            TREATAS ( { DefaultRateCodeId }, Core_ProjectRateCode[ProjectRateCodeId] ),
            TREATAS ( { MatchRateCodeDesc }, Core_ProjectRateCode[ProjectRateCodeDescription] )
        )

    /* FY25 override: only when Project=1 and PRCD is blank */
    VAR StdRate_FY25_Override :=
        IF (
            IsProject1 && ( ISBLANK ( PRCD ) || PRCD = "" ),
            CALCULATE (
                MAX ( Core_ProjectRateCode[StraightTimeRate] ),
                REMOVEFILTERS ( Core_ProjectRateCode ),
                OverrideFilter,
                Core_ProjectRateCode[FY] = 2025   -- adjust if your column is named differently / text "FY25"
            )
        )

    /* Final rate selection (override takes priority) */
    VAR StdRate :=
        COALESCE (
            StdRate_FY25_Override,
            StdRate_RateCode,
            StdRate_Craft,
            StdRate_Emp,
            0
        )

    RETURN
        SWITCH (
            TRUE(),
            ResourceType = "Equipment", StdHours * EquipRate,
            ResourceType = "Labor",     StdHours * StdRate,
            0
        )
)

Quick checks :
• Project field: If it truly is text like “1 A/1 B”, keep the LEFT(...)= "1" logic. If it’s numeric, you can simplify to ProjectRaw = 1.
• Core_ProjectRateCode[FY]: confirm it’s numeric 2025 (change the filter to "FY25" if it’s text).
• Ensure relationships for RELATED() calls are active (Plan → Core_ProjectRateCode, Plan → Core_ProjectEmployee, Plan → Core_ProjectEquipment).
• Make sure there’s a row in Core_ProjectRateCode for the matched description (e.g., “Delivery Manager BASE”) and FY=2025; otherwise the measure will correctly fall back.

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]