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

[Rockz]

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!

[SamWiseOwl · Accepted solution]

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 🙂