Forum Discussion

Responsive Resident

10 months ago

Solved

Help with the dax Formula- ApprovedStdCost

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

Need Help

GrowthNatives

10 months ago

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]

GrowthNatives

Super User

10 months ago

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

Responsive Resident

10 months ago

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

Super User

10 months ago

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
        )
)

Forum Discussion

Help with the dax Formula- ApprovedStdCost

Related content

If formula

formula

DAX Formula Help

DAX Formula

DAX Formula