filtering a value based on a measure

[markhomer]

Hi, all.

My work requires recording a lot of times, usually in the mm:ss.0 (Split) format. I know that Power Bi has problems with this, particularly in charts, so have converted my split format into seconds (a decimal format in excel) for charts and use the split format in tables.

In one report, I have a measure that calculates 90% of a time in seconds, which changes depending on some filters. I would like to display this filtered measure in the split format but am having trouble doing so. I cannot base the measure on the time column so need a way of displaying the split that corresponds to the calculated measure.

Happy to share some data to try and help solve this problem if it helps,

Thanks in advance

[MFelix]

Hi @markhomer ,

Can you share some sample data and expected result?

Regards,

MFelix

[markhomer]

Thanks @MFelix 

Link to example data set and requirements included:

https://1drv.ms/u/s!Aphz4-1LhDG9jwUSWbNK3abXkxE6

many thanks!

Mark

[MFelix]

Hi @markhomer ,

Read this blog post from Greg Deckler.

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Using the formula posted there you can use the following formula:

Duration = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = [90% Time (s)]
// There are 3,600 seconds in an hour
VAR Hours =
    INT ( Duration / 3600)
// There are 60 seconds in a minutea
VAR Minutes =
    INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
// Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )

Should work as wanted.

Check PBIX file attach.

Regards,

MFelix

[markhomer]

Thanks @MFelix this works brilliantly!

I may be pushing my luck, but is there a way of manipulating this DAX to display the result in MM:SS.0 (losing the hour and including tenths of second). This would totally solve my problems!!!

Thanks in advance,

Mark

[MFelix]

Hi @markhomer ,

try the following:

Duration = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = [90% Time (s)]
// There are 3,600 seconds in an hour
VAR Hours =
    INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD( Duration - ( Hours * 3600 );3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 );3600 ); 60 );0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR milliseconds =
    ROUNDUP(MOD(MOD ( MOD( Duration - ( Hours * 3600 );3600 ); 60 );1000);0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros

VAR H =
    IF ( LEN ( Hours ) = 1; 
        CONCATENATE ( "0"; Hours );
        CONCATENATE ( ""; Hours )
      )
// Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1;
        CONCATENATE ( "0"; Minutes );
        CONCATENATE ( ""; Minutes )
    )
// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1;
        CONCATENATE ( "0"; Seconds );
        CONCATENATE ( ""; Seconds )
    )

// milliSeconds with leading zeros
VAR ml =
    IF (
        LEN ( milliseconds ) = 1;
        CONCATENATE ( "00"; milliseconds );
        CONCATENATE ( ""; milliseconds )
    )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
    CONCATENATE (
        H;
        CONCATENATE ( ":"; CONCATENATE ( M; CONCATENATE ( ":"; CONCATENATE(S; CONCATENATE(".";ml) ) ) )
    ))

Not really sure if it works correct.

Please get back to me.

Regards,

MFelix