Forum Discussion

Maximus's avatar
Maximus
Frequent Visitor
3 years ago

Calculating over all run time for each date

Hi,

 

I have used this below query to get the overall run time (excluding overall intervals) for a single date.

But how to get the run time for each of the batch_dates as im representing in graph visuals - the computed data is incorrect.

 

By removing the condition SchedulerInfo[BATCH_DATE]=ALLSELECTED(TenantDetails[BATCH_DATE]) also not helps, 

 

 

Query used:
 
xDayendTimeALL =
    IF(ISBLANK(MAX([PRCS_START_TIME])),BLANK(),
        VAR __Table1 = ALL('SchedulerInfo')
        VAR __Table1_1 = FILTER(__Table1,IF( SchedulerInfo[xCoreSystem] IN VALUES(INTRL_TBL_CORE_SYSTEM[xCoreSystem]) && SchedulerInfo[BATCH_DATE]=ALLSELECTED(TenantDetails[BATCH_DATE]) && SchedulerInfo[LDBID_INL]=ALLSELECTED(TenantDetails[LDBID_INL]) && SchedulerInfo[LDBID]=ALLSELECTED(TenantDetails[LDBID]) && SchedulerInfo[PRCS_STATUS] in values(PRCS_STATUS[PRCS_STATUS]) && SchedulerInfo[xProcessModule] IN VALUES(INTRL_TBL_PROCESS_MODULE[xProcessModule]) &&SchedulerInfo[PROD_LNE] IN VALUES(INTRL_TBL_PROD_LNE[PROD_LNE]),1,0))
        VAR __Start = MIN(SchedulerInfo[PRCS_START_TIME])
        VAR __End = MAX(SchedulerInfo[PRCS_END_TIME])
        VAR __Table = GENERATESERIES(__Start,__End,1/24/60)
        VAR __Table2 = GENERATE(__Table,__Table1_1)
        VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF( [Value]>=[PRCS_START_TIME]  && [Value] <= [PRCS_END_TIME] ,1,0))
        VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",MAXX(CURRENTGROUP(),[Include]))
        VAR cTime=SUMX(__Table4,[Minute])
        VAR _hrs = QUOTIENT ( cTime , 60 )
        VAR _mins = INT ( cTime - _hrs * 60 )
        VAR _sec = MOD ( cTime , 1.0 ) * 60
        RETURN
        FORMAT(_hrs,"00")&":"&FORMAT(_mins,"00")    
    )

 

DAX Direct Query

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Maximus ,

     

    Please try this measure.

    xDayendTimeALL =
    IF(ISBLANK(MAX([PRCS_START_TIME])),BLANK(),
        VAR __Table1 = ALL('SchedulerInfo')
        VAR __Table1_1 = FILTER(__Table1,IF( SchedulerInfo[xCoreSystem] IN VALUES(INTRL_TBL_CORE_SYSTEM[xCoreSystem]) && SchedulerInfo[BATCH_DATE]=SELECTEDVALUE(TenantDetails[BATCH_DATE]) && SchedulerInfo[LDBID_INL]=SELECTEDVALUE(TenantDetails[LDBID_INL]) && SchedulerInfo[LDBID]=SELECTEDVALUE(TenantDetails[LDBID]) && SchedulerInfo[PRCS_STATUS] in values(PRCS_STATUS[PRCS_STATUS]) && SchedulerInfo[xProcessModule] IN VALUES(INTRL_TBL_PROCESS_MODULE[xProcessModule]) &&SchedulerInfo[PROD_LNE] IN VALUES(INTRL_TBL_PROD_LNE[PROD_LNE]),1,0))
        VAR __Start = MIN(SchedulerInfo[PRCS_START_TIME])
        VAR __End = MAX(SchedulerInfo[PRCS_END_TIME])
        VAR __Table = GENERATESERIES(__Start,__End,1/24/60)
        VAR __Table2 = GENERATE(__Table,__Table1_1)
        VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF( [Value]>=[PRCS_START_TIME]  && [Value] <= [PRCS_END_TIME] ,1,0))
        VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",MAXX(CURRENTGROUP(),[Include]))
        VAR cTime=SUMX(__Table4,[Minute])
        VAR _hrs = QUOTIENT ( cTime , 60 )
        VAR _mins = INT ( cTime - _hrs * 60 )
        VAR _sec = MOD ( cTime , 1.0 ) * 60
        RETURN
        FORMAT(_hrs,"00")&":"&FORMAT(_mins,"00")    
    )

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.