Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create Hour from Text

Hi, what I am trying to do is to create a new column (measure) that will calculate the differences between pick begin AND pick end. The result should be in minutes.   The format is like below:   ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    This is not my measure. You can also try by defining a new column as per the calculated column code that I've provided

    DEFINE
COLUMN 'Pick Order'[Column Result] =
VAR PickBegin =
    VALUE ( RIGHT ( 'Pick Order'[PICK Time pick begin], 2 ) ) + 60 * VALUE ( LEFT ( 'Pick Order'[PICK Time pick begin], 2 ) )
VAR PickEnd =
    VALUE ( RIGHT ( 'Pick Order'[PICK Time pick end], 2 ) ) + 60 * VALUE ( LEFT ( 'Pick Order'[PICK Time pick end], 2 ) )
RETURN
    PickEnd - PickBegin

EVALUATE
SUMMARIZECOLUMNS (
    'Pick Order'[PICK Time pick begin],
    'Pick Order'[PICK Time pick end],
    'Pick Order'[Column Result]
)
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

Anonymous Maybe this and make sure you set the Data type to Whole number:

result = (TIME(LEFT([pick end],2), RIGHT([pick end],2),0) - TIME(LEFT([pick begin],2), RIGHT([pick begin],2),0)) * 60 * 24

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you all for your reply. I am using ReportBuilder, testing in DaxStudio. Working on PowerBI DataSets.

     

    And does not matter which of your solution I will choose I am receiving errors like:

    "The result set of a query to external data source has exceeded the maximum allowed size"

     

    DAX code:

    DEFINE

    MEASURE 'Pick Measures'[Time] = 

    VAR _hourbegin =
        LEFT ( SELECTEDVALUE (  'Pick Order'[PICK Time pick begin]), 2 )
    VAR _minbegin =
        RIGHT ( SELECTEDVALUE (  'Pick Order'[PICK Time pick begin] ), 2 )
    VAR _hourend =
        LEFT ( SELECTEDVALUE ( 'Pick Order'[PICK Time pick end] ), 2 )
    VAR _minend =
        RIGHT ( SELECTEDVALUE ( 'Pick Order'[PICK Time pick end] ), 2 )
    VAR _timebegin = _hourbegin * 60 + _minbegin
    VAR _timeend = _hourend * 60 + _minend
    RETURN
        _timeend - _timebegin

    EVALUATE
    SUMMARIZECOLUMNS (

        'Pick DC'[PDC Number],
        'Pick Date'[PD Date],
        'Pick Order'[PICK Order number],
        'Pick Order'[PICK Order pickgroup],
        'Sales Order'[SO Order Number],
        'Material'[MAT Temperature Zone Description],
         'Material'[MAT Business ID],
        'Pick Order'[PICK Time pick begin],
        'Pick Order'[PICK Time pick end],
        'Employee'[EMPL Name],
        FILTER (
            VALUES ( 'Pick DC'[PDC Country] ),
            'Pick DC'[PDC Country] = "USA"
        ),
        FILTER (
            VALUES ( 'Pick DC'[PDC Number] ),
            'Pick DC'[PDC Number] <> 100
        ),
        FILTER (
            'Pick Date',
            'Pick Date'[PD Date]
                = TODAY () - 1
        ),
        
        "Picked Quantity", [Picked Quantity],
        "Picked Net Weight (Kg)", [Picked Net Weight (Kg)],
        "Picked Gross Weight (Kg)", [Picked Gross Weight (Kg)],
        "Time",'Pick Measures'[Time]

    )