Forum Discussion

Rockz's avatar
Rockz
Helper I
2 years ago
Solved

How to Implement User-Defined Input Parameter for Target Processing Time in Power BI direct query

subject : How to Implement User-Defined Input Parameter for Target Processing Time in Power BI direct query mode   Hello Power BI Community,   I need help creating a user-defined input parameter ...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    Hi Rockz 

     

    To create an unlinked slicer go Modeling -> Numeric range:

    Enter the min and max days they can choose, I've called my new column Day Slider:

    This createds a table with a measure capturing what is chosen in the slider.

    Create a new measure referencing that measure (right click the table and choose new measure:

    TargetValue =[Day Slider Value] * 8.5 * 60 (minutes)

     

    If you don't have the ProcessingTime column in your data it would be:

    Processing Time =
    var daysdiff = --Number of days between start and end multiplied by minutes out of hours
     DATEDIFF('Open Closed'[ Issue Created Date ], [ Close Date             ],DAY) *960
     var minsdiff = --minutes between dates less out of hours minutes
    DATEDIFF('Open Closed'[ Issue Created Date ], [ Close Date             ],MINUTE) - daysdiff
    return
    if(
        WEEKNUM([ Issue Created Date ]) <> WEEKNUM([ Close Date             ]) --if they are in the same week
        ,minsdiff --returns calculated minutes
        ,minsdiff + (WEEKNUM([ Close Date             ]) - WEEKNUM([ Issue Created Date ])) * 3840 
    ) --if in different weeks, work out how many weeks and subtract a weekends worth of minutes
     
    Then make a measure:
    TotalProcessingTime = SUM(Table[Processing Time])
     
    Finish with your final measure:
     AchieveTarget = IF([TotalProcessingTime] <= [TargetValue], "Achieve", "Not Achieve")
     
    If this works please mark as a solution for others to find 🙂
SamWiseOwl's avatar
SamWiseOwl
Super User
2 years ago

Hi Rockz 

 

To create an unlinked slicer go Modeling -> Numeric range:

Enter the min and max days they can choose, I've called my new column Day Slider:

This createds a table with a measure capturing what is chosen in the slider.

Create a new measure referencing that measure (right click the table and choose new measure:

TargetValue =[Day Slider Value] * 8.5 * 60 (minutes)

 

If you don't have the ProcessingTime column in your data it would be:

Processing Time =
var daysdiff = --Number of days between start and end multiplied by minutes out of hours
 DATEDIFF('Open Closed'[ Issue Created Date ], [ Close Date             ],DAY) *960
 var minsdiff = --minutes between dates less out of hours minutes
DATEDIFF('Open Closed'[ Issue Created Date ], [ Close Date             ],MINUTE) - daysdiff
return
if(
    WEEKNUM([ Issue Created Date ]) <> WEEKNUM([ Close Date             ]) --if they are in the same week
    ,minsdiff --returns calculated minutes
    ,minsdiff + (WEEKNUM([ Close Date             ]) - WEEKNUM([ Issue Created Date ])) * 3840 
) --if in different weeks, work out how many weeks and subtract a weekends worth of minutes
 
Then make a measure:
TotalProcessingTime = SUM(Table[Processing Time])
 
Finish with your final measure:
 AchieveTarget = IF([TotalProcessingTime] <= [TargetValue], "Achieve", "Not Achieve")
 
If this works please mark as a solution for others to find 🙂