Get value from previous rows

[Anonymous]

I am dealing with an issue to get a value from an earlier row.

I need to get the latest [filtered_top_rated] (for each value from the rows that has [page_type_category] = "Browse Page",

For each row that has page_type_category] in {"Venue Page", "Time-selection Page"}, I need to get the latest [filtered_top_rated] (that comes before) value from the rows that has [page_type_category] = "Browse Page".

[filtered_top_rated] is a filter which if used can be identified only on [page_type_category] = "Browse Page".

There is a sequence of actions. Browse page -> Venue Page -> Time-Selection Page. 

I tried to work with EARLIER() function, but couldn't really get the desired result especially with the rows that are more than one row away from "Browse Page"

collector_timeeventfiltered_top_ratedpage_type_categoryvenue_idRANK

10/23/2017 13:11	page_view	n	Browse Page	NA	8
10/23/2017 13:12	page_view	n	Venue Page	4667db920153525e13f996dab3f135ac	9
10/23/2017 13:12	page_view	n	Venue Page	a6f4826fddfe1db6acfe8ca968536756	10
10/23/2017 13:12	page_view	y	Browse Page	NA	11
10/23/2017 13:12	page_view	n	Venue Page	538e6735bce5d062e3d5fd4f384cff3f	12
10/23/2017 13:13	page_view	n	Time-selection Page	538e6735bce5d062e3d5fd4f384cff3f	13
10/23/2017 13:22	page_view	n	Browse Page	NA	14
10/23/2017 13:22	page_view	n	Browse Page	NA	15
10/23/2017 13:22	page_view	n	Browse Page	NA	16
10/23/2017 13:22	page_view	n	Browse Page	NA	17
10/23/2017 13:23	page_view	n	Venue Page	e7c810dec8b5e2510d29b5bee184b053	18
10/23/2017 13:23	page_view	y	Browse Page	NA	19
10/23/2017 13:24	page_view	n	Venue Page	fbe9bb3254a949e169f85561f410262a	20
10/23/2017 13:24	page_view	n	Venue Page	88e9dd50236be091138ad4fdf192dbec	21
10/23/2017 13:25	page_view	n	Time-selection Page	fbe9bb3254a949e169f85561f410262a	22

[v-yuta-msft · Accepted solution]

@Anonymous ,

There're duplicate times in [collector_time] field so you need to add an index column to build "time" sequence. Click query editor-> Add Column-> Index Column. Then create a calculate column using dax below:

Result = 
IF (
    'Table'[page_type_category] = "Browse Page"
        && 'Table'[collector_time] = MAX ( 'Table'[collector_time] ),
    CALCULATE (
        MAX ( 'Table'[filtered_top_reated] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[collector_time] = MAX ( 'Table'[collector_time] )
        )
    ),
    IF (
        'Table'[page_type_category] IN { "Venue Page", "Time-selection Page" },
        VAR Current_Time_Index = 'Table'[Index]
        VAR Previous_Lastest_Time_Index =
            CALCULATE (
                MAX ( 'Table'[collector_time] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Index] < Current_Time_Index
                        && 'Table'[page_type_category] = "Browse Page"
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'Table'[filtered_top_reated] ),
                FILTER ( ALL ( 'Table' ), 'Table'[collector_time] = Previous_Lastest_Time_Index )
            )
    )
)

 

Community Support Team _ Jimmy Tao

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

[v-yuta-msft · Accepted solution]

@Anonymous ,

There're duplicate times in [collector_time] field so you need to add an index column to build "time" sequence. Click query editor-> Add Column-> Index Column. Then create a calculate column using dax below:

Result = 
IF (
    'Table'[page_type_category] = "Browse Page"
        && 'Table'[collector_time] = MAX ( 'Table'[collector_time] ),
    CALCULATE (
        MAX ( 'Table'[filtered_top_reated] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[collector_time] = MAX ( 'Table'[collector_time] )
        )
    ),
    IF (
        'Table'[page_type_category] IN { "Venue Page", "Time-selection Page" },
        VAR Current_Time_Index = 'Table'[Index]
        VAR Previous_Lastest_Time_Index =
            CALCULATE (
                MAX ( 'Table'[collector_time] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Index] < Current_Time_Index
                        && 'Table'[page_type_category] = "Browse Page"
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'Table'[filtered_top_reated] ),
                FILTER ( ALL ( 'Table' ), 'Table'[collector_time] = Previous_Lastest_Time_Index )
            )
    )
)

 

Community Support Team _ Jimmy Tao

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

[Anonymous · Accepted solution]

Thank you for the response @v-yuta-msft .  With some adjustments I managed to get the result I desired! Thank you!

I have adjusted a bit the code since I realised, that probably I should have given an example with multiple sessions. Additionally to your solution, I edited to find the same session_ID in earlier rows. Also, the last column of sample data, was Ranking (or index) for each session's actions.

Filter Y/N = 
IF (
    'GB_Events'[page_type_category] = "Browse Page"
        && 'GB_Events'[collector_time] = MAX ( 'GB_Events'[collector_time] )
        ,
    CALCULATE (
        MAX ( 'GB_Events'[filtered_top_rated] ),
        FILTER (
            ALL ( 'GB_Events' ),
            'GB_Events'[collector_time] = MAX ( 'GB_Events'[collector_time] )
            && GB_Events[session_id] = EARLIER(GB_Events[session_id])
        )
    ),
    IF (
        'GB_Events'[page_type_category] IN { "Venue Page", "Time-selection Page" },
        VAR Current_Time_Index = 'GB_Events'[RANKX Session]
        VAR Previous_Lastest_Time_Index =
            CALCULATE (
                MAX ( 'GB_Events'[collector_time] ),
                FILTER (
                    ALL ( 'GB_Events' ),
                    GB_Events[session_id] = EARLIER(GB_Events[session_id]) &&
                    'GB_Events'[RANKX Session] < Current_Time_Index
                        && 'GB_Events'[page_type_category] = "Browse Page"
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'GB_Events'[filtered_top_rated] ),
                FILTER ( ALL ( 'GB_Events' ), 'GB_Events'[collector_time] = Previous_Lastest_Time_Index )
            )
    )