To show max/highest number of days

[hidzirf]

Dear All,

I have this set of data and I would like to show in the card the maximum/longer days taken.

If I create measure as Max Value, instead of taking "40 days 1 hour 37 minutes", it shows "8 days 13 hours 35 minutes" as the max value because I believe it read the first digit number 8 is higher than 40.

Appreciate it if anyone can help me with this. Thank you in advance.

[Samarth_18]

Hi @hidzirf ,

Create a column with below code:-

Column = 
VAR ind =
    SEARCH ( "Days", 'Table (3)'[Column1] )
RETURN
    INT ( TRIM ( MID ( 'Table (3)'[Column1], 1, ind - 1 ) ) )

Now create a measure with below code:-

Measure =
VAR _max_date =
    CALCULATE ( MAX ( 'Table (3)'[Column] ), ALL ( 'Table (3)' ) )
RETURN
    CALCULATE ( MAX ( 'Table (3)'[Column1] ), 'Table (3)'[Column] = _max_date )

Outputs:-

Thanks,

Samarth

[hidzirf]

Dear Samarth,

Thank you for your reply. I tried with the code given but got the error "The search Text provided to function 'SEARCH' could not be found in the given text."

I then tried to modify the code and add the argument for the SEARCH function as below but then I got another error with the MID function. >> "An argument of function 'MID' has the wrong data type or has an invalid value."

Column =

VAR ind =

SEARCH ( "Days", Task[duration],, 0 )

RETURN

INT ( TRIM ( MID ( Task[duration], 1, ind -1 ) ) )

Appreciate anyone that can help with this. Thank you.

[Samarth_18]

@hidzirf Convert your new column data type as whole number. It will work.

[hidzirf]

Hi Samarth. Thank you. My new column data type is already set as a whole number. It appears that my error has to do with the fact that I have "Day" and "Hours" in my data as well. I give a new sample of data as below:

Duration

5 Days 20 Hours 13 minutes

30 Days 1 hour 55 minutes

19 Hours 40 minutes

1 Day 23 Hours 55 minutes

For this set of data, I would like to show the longer duration should be 30 Days 1 hour 55 minutes.

Thank you.

Regards,

[Samarth_18 · Accepted solution]

@hidzirf , Create a column with below code:-

Column =
VAR result =
    TRIM ( LEFT ( [Duration], SEARCH ( " D", [Duration], 1, 0 ) ) )
RETURN
    IF ( result = BLANK (), 0, INT ( result ) )

then create a measure with below code:-

Measure = 
VAR _max_date =
    CALCULATE ( MAX ( 'Table'[Column] ), ALL ( 'Table' ) )
RETURN
   CALCULATE ( MAX ( 'Table'[Duration] ), 'Table'[Column] = _max_date )

PFB working solution for reference 🙂

https://we.tl/t-KMGdxaceTx 

[hidzirf]

Hello Samarth,

This is really great! It solved my problem. I thank you so much.

This BI community is awesome. 🙂