Find Minimum Time group by name and sub name

[andershs]

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.

[andershs]

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.

[andershs]

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]))

[Uzi2019]

Hi @andershs 

Try with below measure

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

[Uzi2019]

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

[andershs]

Hi @Uzi2019 

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

Sorry.