Compare events in one table based on date

[Anonymous]

Hi all! 

I have rather ordinary question, but cannot find out how to deal with it. Seems the answer should be rather simple, but... I'll be thankful for any help.

So I have a table with event id (unique key), event type, client name and event time. 

id event_type  client_name  time

1    A               Bob               2019-02-21 12:48:05

2    B               Mari              2019-02-25 08:48:05

3    C               Alex              2019-01-25 12:48:08

4    A               Mari              2019-02-23 13:48:05

5    B               Bob               2019-02-10 12:48:05

6    B               Alex              2019-01-11 11:33:01

7    A               Alex              2019-01-23 09:00:02

8    C               Bob               2019-02-13 12:30:05

I need to compare my events based on date:

1) find all cases when clients have event A after event B

2) find average time diff between B and C events

Seems these both problems have similar solution. I tried "earlier" and variables, but haven't figured out how to make them work for the case. 

[v-yulgu-msft]

Hi @Anonymous ,

Please try below measures:

If EventA after EventB =
VAR eventB_time =
    CALCULATE (
        MAX ( Sheet11[time] ),
        FILTER (
            ALLEXCEPT ( Sheet11, Sheet11[client_name] ),
            Sheet11[event_type] = "B"
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( Sheet11[event_type] ) = "A"
            && MAX ( Sheet11[time] ) > eventB_time,
        "Yes",
        "No"
    )

diff between B & C =
VAR eventC_time =
    CALCULATE (
        MAX ( Sheet11[time] ),
        FILTER (
            ALLEXCEPT ( Sheet11, Sheet11[client_name] ),
            Sheet11[event_type] = "C"
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( Sheet11[event_type] ) = "B",
        DATEDIFF ( MAX ( Sheet11[time] ), eventC_time, SECOND )
    )

average diff = AVERAGEx(Sheet11,[diff between B & C])

Best regards,

Yuliana Gu

[Anonymous]

I planned to use this query like a dax-request while importing data from analysis services, cause no need to import all the huge client table to my report. Today I tried to import all the table just for last several days and created a calculated column using the query. As expected, selectedvalue works fine in power bi calculated column Unfortunatly I received all "No" values in the column, even for those ids, for which I definetly know "yes" should be displayed. Can't find a mistake, seems everything is logical and should work... 

[Anonymous]

Yuliana, thank you!

I received an error for the first query "Failed to resolve name 'SELECTEDVALUE'. It is not a valid table, variable, or function name." I'm using this dax for quering olap, if this matters.