Inbound and Outbound calculation in Dax

[karthickpbi]

Hi Friends,

Could you please help me to  acheve the below scenario for resulted column(Resulted1, Resulted Inbound date, Result count of direction for Inbound), 

I have tried multiple time, but its showing worng value, it would be great if you can help on this.

Can we achieve below Scenarion with Calculated column or Measure.

Note:1. Inbound will not be consider if we don't have outbound

Note:2. first Outbound will be consider if we have one or more  Inbound

Note 3. Resulted inbound date is respective of first Inbound date and Time. 

Case ID	Date and Time	Direction	Result1	Result Inbound Date	Result: Count Inbound for Each Case
101	01-01-2022:09:00:00	INBOUND	1		1
101	01-01-2022 09:20:00	OUTBOUND	0	01-01-2022:09:00:00
101	01-01-2022 09:30:00	OUTBOUND
101	01-01-2022 14:50:00	INBOUND	1		1
101	01-01-2022 09:50:00	INBOUND	1		1
101	01-01-2022 01:00:00	INBOUND	1		1
101	01-01-2022 10:20:00	OUTBOUND	0	01-01-2022 14:50:00
101	02-01-2022 08:45:00	OUTBOUND
101	02-01-2022 09:35:00	OUTBOUND
101	02-01-2022 09:00:00	OUTBOUND
101	02-01-2022 10:00:00	INBOUND	1		1
102	02-01-2022 11:40:00	Outbound	0	02-01-2022 10:00:00
102	02-01-2022 12:20:00	INBOUND	1		1
102	03-01-2022 09:00:00	INBOUND	1		1
102	03-01-2022 09:15:00	OUTBOUND	0	02-01-2022 12:20:00
102	03-01-2022 11:00:00	INBOUND	1		1
102	04-01-2022 09:10:00	OUTBOUND	0	03-01-2022 11:00:00
102	04-01-2022 18:35:00	OUTBOUND
102	04-01-2022 11:00:00	OUTBOUND
102	04-01-2022 11:20:00	INBOUND	1

Thanks in Advance.

[karthickpbi]

Even I iried to Implement with Case Wise , Getting Error.

var _a = CALCULATE(MAX('Table'[Index]),FILTER('Table',[Index]<EARLIER('Table'[Index]) && 'Table'[Case ID] = EARLIER('Table'[Case ID]) &&[Result 1]=BLANK()))

[karthickpbi]

HI @v-jianboli-msft ,

Thanks for sharing the Solution, when I'm Implementing the solution getting below error.

"<pi>There's not enough memory to complete this operation. Please try again later when there may be more memory available.</pi>"

[v-jianboli-msft]

Hi @karthickpbi ,

Please try:

First, create a column for index:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZK7CsMwDEV/RXhOQVIcSD2WLl2SpZmCl9K5XZr/rxvqJH5hJ2BjY3yupCuNoyAkUQmkk1mMzArPCtEs83rrLv3QXc3t9wfmU1chA4bhP9MPdwthUhiSMnUoA8uOQiRVcyDf/Qzt94Uw78u2AKdEXkO3SjZlvrBj5hEICzvA2yrzzrDHkJI20PR5vKfX0zoTFYaEDC8GZ0LXsRr3MBSa6ae7JgNxGSqZoZmRTujEDEWFIS5Dbck4+BCVjEMIpXsitP4C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case ID" = _t, #"Date and Time" = _t, Direction = _t, Result1 = _t, #"Result Inbound Date " = _t, #"Result: Count Inbound for Each Case" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case ID", Int64.Type}, {"Date and Time", type text}, {"Direction", type text}, {"Result1", Int64.Type}, {"Result Inbound Date ", type text}, {"Result: Count Inbound for Each Case", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Result1", "Result Inbound Date ", "Result: Count Inbound for Each Case"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Then create these cloumns:

Result 1 =
SWITCH (
    [Direction],
    "INBOUND", 1,
    "OUTBOUND",
        IF (
            MAXX (
                FILTER ( 'Table', [Index] = EARLIER ( 'Table'[Index] ) - 1 ),
                [Direction]
            ) = "INBOUND",
            0,
            BLANK ()
        )
)

Result Inbound Date =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            'Table',
            [Index] < EARLIER ( 'Table'[Index] )
                && [Result 1] = BLANK ()
        )
    )
VAR _b =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( 'Table', [Index] > _a && [Index] < EARLIER ( 'Table'[Index] ) )
    )
RETURN
    IF (
        [Result 1] = 0,
        CALCULATE ( MAX ( 'Table'[Date and Time] ), FILTER ( 'Table', [Index] = _b ) ),
        BLANK ()
    )


Result: Count Inbound for Each Case =
VAR _a =
    CALCULATE (
        MIN ( [Index] ),
        FILTER (
            'Table',
            [Direction] = "OUTBOUND"
                && [Index] > EARLIER ( 'Table'[Index] )
        )
    )
RETURN
    IF ( [Direction] = "INBOUND", IF ( _a = BLANK (), BLANK (), 1 ), BLANK () )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.