Convert Calculated column to measure

[Anonymous]

Hi All,

I have some dynamic values passing through a application and storing in a measure in power bi, in current requirement im using that measure in  calculated column for a comprison and giving the result , but every time  measure stores a new value i have to refresh my dataset to get the latest value because calculated column are precomputed and stored in power bi where as measures are runtime computation, so my requirement is to convert the below calculated column to a measure.

Calculated column: Help me to convert the below calculated query to a measure query

Target_hrs=

var calc_hour_c = If('Case'[Priority] In {"critical"} && 'Case'[CaseStatus] In {"open"},DATEDIFF('Case'[CaseCreatedDate], 'Case'[CaseClosedDateTime],Hour))

var case_target_c = IF(calc_hour_c<=[TargetCritical],calc_hour_c)

return var case_target_c

**TargetCritical** is  measure which stored dynamic value.

Thanks in advace,

Venkat

[Anonymous]

No, because the datediff used in the expression is always giving me the difference between the maximun date of case created and maximum date of closed date.

[v-easonf-msft]

Hi, @Anonymous 

Please share your sample data for further research.

This will better help us understand your question.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Kindly let me know to add sample data and PBIX file.

[v-easonf-msft]

Hi, @Anonymous 

Considering data security, it is recommended that you remove sensitive content from the original pbix. You can upload a simplified copy of the model and desired results to your cloud storage and then share them as a link.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , based on need use sumx of averagex

Target_hrs=
var calc_hour_c = Sumx(filter('Case','Case'[Priority] In {"critical"} && 'Case'[CaseStatus] In {"open"}),DATEDIFF('Case'[CaseCreatedDate], 'Case'[CaseClosedDateTime],Hour))

var case_target_c = IF(calc_hour_c<=[TargetCritical],calc_hour_c)

return var case_target_c

if needed force context using values or summarize

[Anonymous]

My requirement is to create a new measure with the above  formula which i used in calculated column, if i do that way then  my comparsion would be between a measure and another measure which will defeat the purpose of dataset refresh everytime target critcal measure brings a new value.

[v-easonf-msft]

Hi,  @Anonymous 

Not sure what you want.

You may try formula as below:

Target_hrs =
VAR calc_hour_c =
    IF (
        MAX ( 'Case'[Priority] )
            IN { "critical" }
                && MAX ( 'Case'[CaseStatus] ) IN { "open" },
        DATEDIFF (
            MAX ( 'Case'[CaseCreatedDate] ),
            MAX ( 'Case'[CaseClosedDateTime] ),
            HOUR
        )
    )
VAR case_target_c =
    IF ( calc_hour_c <= [TargetCritical], calc_hour_c )
RETURN
    case_target_c

Best Regards,
Community Support Team _ Eason