Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  DAX to calculate Difference from Min time from another table

    Posted Dec 29, 2021 06:26 PM
    Edited by Mark Soares Dec 29, 2021 06:30 PM
    Hello,
    I have two tables (Table 1 and 2) as below (or per picture attached) and they have relationship between ID.

    I would like to know if there is a unique DAX expression to calculate the minimum time, per ID, from Table 1 and calculate the Difference time in minutes against the respective time in ID from Table 2 (according to expected result example in the last column below).
    Table 1 Table 2 Minimum Time Expected result
    ID Date_Hour_A ID Date_Hour_B ID Date_Hour_A Diff (Hour_B - Hour_A) Minutes
    1 11/29/21 2:10 AM 1 11/29/21 1:10 AM 1 01:50 00:40
    1 11/29/21 1:50 AM 2 11/2/21 8:30 AM 2 09:00 00:30
    2 11/2/21 9:00 AM 3 12/14/21 4:35 AM 3 05:29 00:54
    3 12/14/21 5:29 AM 4 11/8/21 5:25 AM 4 07:29 02:04
    3 12/14/21 6:43 AM 5 10/3/21 10:06 AM 5 10:47 00:41
    4 11/8/21 7:29 AM 6 12/6/21 8:02 AM 6 08:25 00:23
    5 10/3/21 11:03 AM
    5 10/3/21 10:47 AM
    5 10/3/21 11:00 AM
    6 12/6/21 8:25 AM
    6 12/6/21 9:30 AM
    6 12/6/21 8:34 AM

    Really appreciate any help from community.

    Regards,
    Mark

    ------------------------------
    Mark Soares
    BI Analist
    Campinas
    ------------------------------


  • 2.  RE: DAX to calculate Difference from Min time from another table

    Top Contributor
    Posted Jan 04, 2022 02:25 PM
    Hi @Mark Soares,

    Assuming Table 2 with Date_Hour B has single entries for all the IDs, then you have a one to many relationship between Table 2 and Table 1.
    With the tables connected you can use the following measure to get the time difference:
    Time Diff (B-A) = MIN('Table 2'[Date_Hour_B] ) - MIN('Table 1'[Date_Hour_A] )​
    ​If you format this as hh:mm then you should get the results you were after.  In your example, all these date / time pairs from the two tables are on the same day - you might want to check if this is always the case and how the measure behaves if it isn't.

    If you actually want the number of minutes then you can convert like this:
    Minutes Diff (B-A) = 
    VAR myDateTime = MIN('Table 2'[Date_Hour_B] ) - MIN('Table 1'[Date_Hour_A] )
    RETURN
    MINUTE( myDateTime ) + 60* HOUR( myDateTime )​

    Hope that helps.

    ------------------------------
    Simon Lamb
    IT Consultant, Method Excel Ltd.
    ------------------------------



  • 3.  RE: DAX to calculate Difference from Min time from another table

    Posted Jan 15, 2022 01:54 PM

    Hi @Simon Lamb
    Thank you for your reply with proposal solution, but the expression didn't work properly for my needs.

    After some exhaustive and additional tests, the best solution I got here was using (DateDiff, Calculate and MIN) in the same expression like this,

    DATEDIFF('Table 2'[Date_Hour_B],CALCULATE(MIN('Table 1'[Date_Hour_A]),ALLEXCEPT(Table 2, Table 2[ID])),MINUTE)


    Best Regards,
    Mark



    ------------------------------
    Mark Soares
    BI Analist
    Campinas
    ------------------------------