topic Comparison of Values from different rows in DAX Commands and Tips

Comparison of Values from different rows

Gorgmaster — Fri, 06 Sep 2019 11:31:07 GMT

Hey there!

Got a little Problem with some data.

For better understanding here an example:

I've got a table with time stamps of machined parts. The time stamp tells me, when the part was finished. I would like to take the time stamp of the last part as start time for the next part. So I will get a time range, during which time a part was produced. In a second tabel I can compare the time ranges to error messages. That I already solved.

Here is an example for the tabel, the last column should be calculated.

machine	part	End Time	Start Time
1	1	07:30	Blank
2	2	07:29	07:22
1	3	07:37	07:30
1	4	07:44	07:37
2	5	07:36	07:29
1	6	07:51	07:44
2	7	07:22	Blank
1	8	07:58	07:51

I tried to find the Min-Value of a filtered list where the row of the actual value is excepted. But I didn't get to a solution.

Hopefully someone can help!

Re: Comparison of Values from different rows

sturlaws — Sat, 07 Sep 2019 13:17:11 GMT

Hi,

try this

Start time =
CALCULATE (
    MAX ( 'Table'[End Time] );
    FILTER (
        'Table';
        'Table'[machine] = EARLIER ( 'Table'[machine] )
            && 'Table'[part] < EARLIER ( 'Table'[part] )
    )
)

Re: Comparison of Values from different rows

Gorgmaster — Mon, 09 Sep 2019 06:29:04 GMT

Hey,

thanks for the reply.

Unfortunately it doesn't work. I've tried the formula with the exact given table in my question. But I got in the start-column the same time as in the end column.

By the way, the part number isn't meant as a rank. it is only a number.

Re: Comparison of Values from different rows

sturlaws — Mon, 09 Sep 2019 07:29:56 GMT

So a slight modification to the code then:

Start time =
VAR _prevTime = 'Table'[End Time]
VAR _machine = 'Table'[machine]
RETURN
    CALCULATE (
        MAX ( 'Table'[End Time] );
        FILTER ( 'Table'; 'Table'[machine] = _machine && 'Table'[End Time] < _prevTime )
    )

And just to be sure, you are trying to create a calculated column, right?

This works on the sample data you provided. If you have trouble implementing it in your model, perhaps post a more realistic dataset?

Re: Comparison of Values from different rows

Gorgmaster — Mon, 09 Sep 2019 07:37:52 GMT

That's it!

Thanks a lot!