Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi @guineapig ,
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
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...
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |