Forum Discussion

DazzaG's avatar
DazzaG
New Member
3 years ago

assistance with DAX code please

hi team, im fairly new to power BI and am helping my colleague with some reporting.  We are using a report created by someone else who has since left the organisation. The  report displays some averages using DAX query but the results appear to be very generous or low. I believe that something in the DAX formular or the way the data is displayed isnt right and would appreciate some assistance trying to work through it.

 

One example is Average Manger approval time which the result is 00:01:49 . Looking at the data [Manager Approval] it should be alot highter. A snippet of the [Manager Approval] data is below. Thats not all the data just a sample

 

 

 the DAX query is 

 

Avg Manager Approval Time =

VAR DecimalMinutes = AVERAGE(TimeIntelligence[Manager Approval])

VAR Duration = DecimalMinutes * 60

VAR Hours = INT(DIVIDE(Duration,3600,0))

VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))

VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))

VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

RETURN

IFERROR(
    IF (DecimalMinutes>0,
    H & ":" & M & ":" & S,
    "0:00"),
    "Error")

 

thanks in advance

Need Help

1 Reply

  • ToddChitt's avatar
    ToddChitt
    Super User
    3 years ago

    This is the Power Query forum. You may find better luck in the Desktop forum. But since you're here:

    Can you convert the time interval to a base of seconds or minutes, (i.e.: some common integer value) then calculate a simple AVERAGE DAX Measure from that and compare the two? For example, if the existing report shows a single Manager's Average Time as 00:01:49 (DD:HH:MM ?) and your simple DAX Average is 109 minutes, then you know it is right.