Min with Filter

[cajste]

Hi,

Based on the table below, I'm trying to create a calculated measure that returns the Deviation value for the MIN value from the column TimeAtLocation for the rows that have the same PathIdent-Departure value as the present row. The wanted result is in the last column of the table.

I've tried combinations of CALCULATE and FILTER but have not been able to get the correct result. Any help would be much appreciated!

//Caj

TimeAtLocation	Deviation	PathIdent-Departure	Wanted result
2012-04-04 16:34	-11	41612 - 2012-04-04 16:34:00	-11
2012-04-04 16:44	1	41612 - 2012-04-04 16:34:00	-11
2012-04-04 16:57	2	41612 - 2012-04-04 16:34:00	-11
2012-04-04 17:08	1	41612 - 2012-04-04 16:34:00	-11
2012-04-04 17:20	1	41612 - 2012-04-04 16:34:00	-11
2012-04-04 17:32	3	41612 - 2012-04-04 16:34:00	-11
2012-05-11 16:34	-16	41612 - 2012-05-11 16:34:00	-16
2012-05-11 16:44	2	41612 - 2012-05-11 16:34:00	-16
2012-05-11 16:57	4	41612 - 2012-05-11 16:34:00	-16
2012-05-11 17:08	3	41612 - 2012-05-11 16:34:00	-16
2012-05-11 17:20	1	41612 - 2012-05-11 16:34:00	-16
2012-05-11 17:32	1	41612 - 2012-05-11 16:34:00	-16
2012-05-11 17:43		41612 - 2012-05-11 16:34:00	-16
2012-05-11 17:50		41612 - 2012-05-11 16:34:00	-16

<tbody>
</tbody>

 

[MD610]

This should work as a Calculated Column:

=CALCULATE(MIN(TableName[Deviation]), ALLEXCEPT(TableName, TableName[PathIdent-Departure]))

It should also work as a measure as long as your PivotTable contains PathIndent-Departure in the rows.

 

[cajste]

Thanks Mike!

I need the code to check for all rows with the same PathIdent-Departure, determine which one of those rows that has the earliest TimeAtLocation and return the value in the Deviation column for that row. In the example above, for PathIdent-Departure 41612 - 2012-04-04 16:34:00, the earliest TimeAtLocation is 2012-04-04 16:34 and the Deviation value for that row is -11.

It seems as if the code you suggested returns the min Deviation for a specific set of PathIdent-Departure, not taking into account the earliest TimeAtLocation. Is it possible to change the code to do that?

//Caj

 

[MD610]

Oh I see. Based on the sample data and wanted column you provided, I thought you were just trying to ID the min Deviation for a given Path.

There is probably a more optimized way to accomplish what you want, but this is what I came up with quickly on the spot:

=CALCULATE(
                   MINX(
                          SUMMARIZE(TableName, 
                                           TableName[PathIdent-Departure], 
                                           "MinDev",
                                            LOOKUPVALUE(TableName[Deviation],
                                                                  TableName[TimeAtLocation],
                                                                  MIN(TableName[TimeAtLocation])
                                             )
                           ),
                           [MinDev]
                    ), 
                    ALLEXCEPT( TableName, TableName[PathIdent-Departure])
)

 

[cajste]

Mike, I really appreciate the help!

I tried the code but get an error like this (my translation): "Calculation error in column V_TrainRunningInfo'[]: A table with more than one value were stated, but only one value were expected". Am I doing something wrong or is there something in the data that causes this?

The actual code looks like this

=CALCULATE(
MINX(
SUMMARIZE(V_TrainRunningInfo;
V_TrainRunningInfo[PathIdent-Departure];
"MinDev";
LOOKUPVALUE(V_TrainRunningInfo[Deviation];
V_TrainRunningInfo[TimeAtLocation];
MIN(V_TrainRunningInfo[TimeAtLocation])
)
);
[MinDev]
);
ALLEXCEPT(V_TrainRunningInfo;V_TrainRunningInfo[PathIdent-Departure])
)

//Caj

 

[MD610]

Hmm...does the column V_TrainRunningInfo[TimeAtLocation] contain unique values?

It appears to in the sample data, but if it doesn't in reality, that could be tripping up the LOOKUPVALUE() function.

 

[cajste]

No, V_TrainRunningInfo[TimeAtLocation] does not contain unique values. However V_TrainRunningInfo[TimeAtLocation] should be unique for each set of [PathIdent-Departure].

//Caj

 

[MD610]

Then as is, LOOKUPVALUE() won't work. I'll have to give some thought to a more elegant solution.

In the meantime, if the combination of [TimeAtLocation] and [PathIdent-Departure] are unique to each row, you could create a unique ID that would work with LOOKUPVALUE() for now.

Add a Calc Column called ID with this formula:

=RANKX(TableName, [TimeAtLocation]&"_"&[PathIdent-Departure],,1)

The double comma is not a typo. This new ID field should be unique for each row if the combination of the 2 fields is unique.

Then modify the LOOKUPVALUE() portion to reference the [ID] instead of [TimeAtLocation]:

=CALCULATE(
                   MINX(
                          SUMMARIZE(TableName, 
                                           TableName[PathIdent-Departure], 
                                           "MinDev",
                                            LOOKUPVALUE(TableName[Deviation],
                                                                  TableName[ID],
                                                                  MIN(TableName[ID])
                                             )
                           ),
                           [MinDev]
                    ), 
                    ALLEXCEPT( TableName, TableName[PathIdent-Departure])
)

Like I said, there is probably a more efficient way, but I will have to give it some thought.

 

[cajste]

Thanks, I'll give a try tomorrow!

//Caj

 

[cajste]

Thanks Mike, I had to add some information to the ID column to make it unique and it seems to be working just as it should!

Of course, when one question is answered the next is asked. Each time we have an increase in deviation that is 3 minutes or more compared to the previous station and the total deviation is more than 3 minutes, we are fined a certain amount of money. So what I need to do is to determine, for each row, is the deviation for the previous row within the ID and return the increase in deviation only if it is larger the 3 and the deviation is larger then 3. If it is the first row for that ID then it should only return the deviation if it is larger then 3.

Following that question; is there a pattern in where we have the increases in deviation? To answer that question I need to rank the rows within each ID beginning with the first row, based on TimeAtLocation. Then I can compare all the IDs within each PathIdent and get the answer.

I tried to do something on the first question based on your solution, I assume they are somewhat similar, but haven't been able to get it right. The second I have no idea on how to handle.

If you have any quick thoughts in this I would really appreciate it!

//Caj