Forum Discussion
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 e...
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]))