Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Calculated Column to Measure

Hi can anyone help me to change to measure my calculated column in TRS Table, column (Overall_Cum_PT_YTD)    Overall_Cum_PT_YTD = VAR CurrentDate = 'TRS'[WeekNo:] VAR Filteredtable = FILTER('TRS',...
Need Help
  • quantumudit's avatar
    quantumudit
    1 year ago

    Hello AllanBerces 

    To make the formula work when the [used_Area] column is in context, you can use the following DAX instead:

     

    Overall_Cum_PT_YTD_Measure = 
VAR CurrentDate =
    SELECTEDVALUE ( TRS[WeekNo:] )
VAR Filteredtable =
    FILTER (
        ALL ( 'TRS' ),
        TRS[WeekNo:] <= CurrentDate
            && TRS[Type] <> "Non-productive"
    )
RETURN
    IF (
        ISINSCOPE ( TRS[used_AREA] ),
        CALCULATE (
            SUM ( 'TRS'[NPTHrs] ),
            Filteredtable,
            VALUES ( TRS[used_AREA] )
        ),
        CALCULATE (
            SUM ( 'TRS'[NPTHrs] ),
            Filteredtable
        )
    )

     

    I am attaching the Power BI with modified formula in the attachment.

     

    Best Regards,
    Udit


    If this post helps, then please consider Accepting it as the solution to help other members find it more quickly.
    Appreciate your Kudo 👍

    🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
    ✨ Visit My Linktree: LinkTree

    Proud to be a Super User

quantumudit's avatar
quantumudit
Super User
1 year ago

Hello AllanBerces 
Please use the following DAX to create the measure with the same logic that you have used for creating the calculated column.

 

Overall_Cum_PT_YTD_Measure = 
VAR CurrentDate = SELECTEDVALUE(TRS[WeekNo:])
VAR Filteredtable = FILTER(
    ALL('TRS'),
    TRS[WeekNo:] <= CurrentDate 
    && TRS[Type]  <> "Non-productive"
)

RETURN
CALCULATE(
    SUM('TRS'[NPTHrs]), 
    Filteredtable
)

 

Additionally, there is a BSPTA calculated column in your TRS that is erroneous. Make sure to fix it or delete it, as it might create issues testing other aspects.

 

I am also attaching the Power BI file for your reference.

 

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

 

Proud to be a Super User