Forum Discussion

andershs's avatar
andershs
Frequent Visitor
2 years ago

Find Minimum Time group by name and sub name

I have the following set up:

For each date, I have a Name followed by Departure and Arrival (times). Under each Name I have a SubName which can be null.

What I need to find is: for each date, for each name, find the first Departure (time) within the SubName. Possibly both considering Null-values and not considering Null-values.

Similarly for the last Arrival (time).

So somehow, a group by issue in SQL terms.

 

 

I have tried different Filter() and Calculate() options as calculated columns or SummarizeColumns() as a measure.

But I have to admit, that my knowledge of DAX is not great enough to solve this issue currently. I cannot seem to find the correct approach in filtering etc.

Any help would be appreciated.

 

Need Help

5 Replies

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi andershs 
    can you share the data like copy paste?? so that we can work on your data.

     

     

    • andershs's avatar
      andershs
      Frequent Visitor
      2 years ago

      Hi Uzi2019 

      Unfortunately, I cannot share the data. Company Policy. I know it is not optimal.

      Sorry.

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi andershs 

    Try with below measure

    First= calculate(Min(Departure), Allselected(Subname, Name)
    last = Calculate(max(Arrival),Allselected(Subname))

     

     

  • andershs's avatar
    andershs
    Frequent Visitor
    2 years ago

    Hi Uzi2019 

    Thanks for your answer. It is much appreciated.

    Your measure is equal to what I have created in measure #Test. It gives the minimum value for each row, which is not what I need.

    Below, you can see, I have tried the following calculated columns and measures. I think FirstSubTime and FirstSubTimeNotNull is pretty close. But the first does not handle SubName null-values well, and the latter only gives values for SubName <> Null.

     

    To make matters more complicated, I am currently using decimal hours for my columns and measures (one problem at a time). And the divide by (60 * 60) is due to my values actually being stored in seconds.

     

    FirstSubTime = 
        var name = MyTable[Name]
        var subName = MyTable[SubName]
        var myDate = MyTable[Date]
        var dateRows = FILTER(MyTable, MyTable[Date] = myDate && MyTable[Name] = name && MyTable[SubName] = subName)
        var firstDep = minx(dateRows, MyTable[Departure])
    return 
    CALCULATE(Min(MyTable[Departure]) / (60*60), Filter(dateRows, firstDep = MyTable[Departure] && MyTable[Date] = myDate && MyTable[SubName] = subName))
     
     
    FirstSubTimeNotNull = 
        var name = MyTable[Name]
        var subName = MyTable[SubName]
        var myDate = MyTable[Date]
        var dateRows = FILTER(MyTable, MyTable[Date] = myDate && MyTable[Name] = name && MyTable[SubName] = subName && subName > 0) -- SubName = None is actually SubName = -1, therefore > 0
        var firstDep = minx(dateRows, MyTable[Departure])
    return 
    CALCULATE(Min(MyTable[Departure]) / (60*60), Filter(dateRows, firstDep = MyTable[Departure] && MyTable[Date] = myDate && MyTable[SubName] = subName))
     
    #Test = minx(
        ADDCOLUMNS(
            SUMMARIZECOLUMNS('MyTable'[Date], MyTable[Name], MyTable[SubName]),
            "@Test", [#MinDeparture]
        ),
        [@Test]
    )
     
    #MinDeparture = min(MyTable[Departure]) / (60 * 60)
     
    And of course
     
    #First = calculate(Min(MyTable[Departure]) / (60 * 60), Allselected(MyTable[SubName]))

     

     

     

     

     

  • andershs's avatar
    andershs
    Frequent Visitor
    2 years ago

    My thinking is: I need to come up with a filtering that filters on either (SubName or Departure >= min(Departure within Sub Name)), in order to include values from both the SubName and null values within the SubName.

    I just don't know exactly how.