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 in Power BI to calculate target processing times for issues. Users should be able to set a value X (in days), which will be used to determine if issues meet the target processing time.

 

### Key Requirements:

 

  1. *User Input Parameter:*

   - Users set X via a slicer.

   - TargetValue = X * 8.5 * 60 (minutes).

 

  1. *Calculations:*

   - Calculate processing time in minutes.

   - Determine if processing time meets the target:

     DAX

     AchieveTarget = IF([TotalProcessingTime] <= [TargetValue], "Achieve", "Not Achieve")

    

 

  1. *Working Hours:*

   - Exclude non-working hours (Monday to Friday, 8:00 AM to 4:30 PM) in processing time calculations.

 

### Example Data:

 

| Status | Issue No | Issue Created Date | Close Date             |    Processing Time        | Achieve Target |

|--------|-----------|----------------------|---------------------|--------------------------|----------------|

| Closed | 12345    | 2024-03-11 09:00   | 2024-03-12 12:00 | 1020 mins                     | Achieve |

| Open   | 12346    | 2024-03-11 11:00   | -                            | 1440 mins (up to now) | Not Achieve |

 

### Need Help With:

 

  1. *Creating a parameter for user input.*
  2. *Using this parameter in DAX calculations.*
  3. *Accurately calculating processing time considering only working hours.*
  4. *Dynamically updating visuals based on the user-defined parameter.*

 

Thank you for your assistance!

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 🙂

1 Reply

  • 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 🙂