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.
I wrote DAX functions to show the beginning (using MIN()) and ending (using MAX()) times of events occurring within a test, with different tests denoted by a Test ID and different events noted by an Event ID. For some of the Test IDs the measures work to give me the correct beginning and ending times of the events. However, for some of the Test IDs, the event beginning and end times are showing backwards, i.e. what should be the min time is showing as the max time and vice versa. The table below shows a sample of data. To note: Event ID Manual and Event ID Raw are basically the same thing with a few differences. The formatting of the sample data is similar to the actual data, so though it may look strange it is correct.
Test ID | Event ID Manual | Event ID Raw | Event Point | Date | Time |
1 | 1 | 1 | 1 | 10/1/2019 | 06:00:00AM |
1 | 1 | 1 | 2 | 10/1/2019 | 06:01:00AM |
1 | 1 | 1 | 3 | 10/1/2019 | 06:02:00AM |
1 | 1 | 1 | 4 | 10/1/2019 | 06:03:00AM |
1 | 1 | 1 | 5 | 10/1/2019 | 06:04:00AM |
1 | 1 | 1 | 6 | 10/1/2019 | 06:05:00AM |
1 | |||||
1 | |||||
1 | 2 | 2 | 1 | 10/1/2019 | 07:00:00AM |
1 | 2 | 2 | 2 | 10/1/2019 | 07:01:00AM |
1 | 2 | 2 | 3 | 10/1/2019 | 07:02:00AM |
1 | 2 | 2 | 4 | 10/1/2019 | 07:03:00AM |
1 | 2 | 2 | 5 | 10/1/2019 | 07:04:00AM |
1 | 2 | 2 | 6 | 10/1/2019 | 07:05:00AM |
1 | |||||
1 | |||||
1 | 3 | 3 | 1 | 10/1/2019 | 08:00:00AM |
1 | 3 | 3 | 2 | 10/1/2019 | 08:01:00AM |
1 | 3 | 3 | 3 | 10/1/2019 | 08:02:00AM |
1 | 3 | 3 | 4 | 10/1/2019 | 08:03:00AM |
1 | 3 | 3 | 5 | 10/1/2019 | 08:04:00AM |
1 | 3 | 3 | 6 | 10/1/2019 | 08:05:00AM |
2 | 1 | 1 | 10/10/2019 | 06:00:00AM | |
2 | 1 | 1 | 2 | 10/10/2019 | 06:01:00AM |
2 | 1 | 1 | 3 | 10/10/2019 | 06:02:00AM |
2 | 1 | 1 | 4 | 10/10/2019 | 06:03:00AM |
2 | 1 | 1 | 5 | 10/10/2019 | 06:04:00AM |
2 | 1 | 6 | 10/10/2019 | 06:05:00AM | |
2 | |||||
2 | 2 | 1 | 10/10/2019 | 07:00:00AM | |
2 | 2 | 2 | 2 | 10/10/2019 | 07:01:00AM |
2 | 2 | 2 | 3 | 10/10/2019 | 07:02:00AM |
2 | 2 | 2 | 4 | 10/10/2019 | 07:03:00AM |
2 | 2 | 2 | 5 | 10/10/2019 | 07:04:00AM |
2 | 2 | 6 | 10/10/2019 | 07:05:00AM | |
3 | 1 | 1 | 1 | 10/1/2019 | 06:00:00AM |
3 | 1 | 1 | 2 | 10/1/2019 | 06:01:00AM |
3 | 1 | 1 | 3 | 10/1/2019 | 06:02:00AM |
3 | 1 | 1 | 4 | 10/1/2019 | 06:03:00AM |
3 | 1 | 1 | 5 | 10/1/2019 | 06:04:00AM |
3 | 1 | 1 | 6 | 10/1/2019 | 06:05:00AM |
3 | |||||
3 | |||||
3 | 2 | 2 | 1 | 10/1/2019 | 07:00:00AM |
3 | 2 | 2 | 2 | 10/1/2019 | 07:01:00AM |
3 | 2 | 2 | 3 | 10/1/2019 | 07:02:00AM |
3 | 2 | 2 | 4 | 10/1/2019 | 07:03:00AM |
3 | 2 | 2 | 5 | 10/1/2019 | 07:04:00AM |
3 | 2 | 2 | 6 | 10/1/2019 | 07:05:00AM |
3 | |||||
3 | |||||
3 | 3 | 3 | 1 | 10/1/2019 | 08:00:00AM |
3 | 3 | 3 | 2 | 10/1/2019 | 08:01:00AM |
3 | 3 | 3 | 3 | 10/1/2019 | 08:02:00AM |
3 | 3 | 3 | 4 | 10/1/2019 | 08:03:00AM |
3 | 3 | 3 | 5 | 10/1/2019 | 08:04:00AM |
3 | 3 | 3 | 6 | 10/1/2019 | 08:05:00AM |
3 | |||||
3 | 4 | 4 | 1 | 10/1/2019 | 09:00:00AM |
3 | 4 | 4 | 2 | 10/1/2019 | 09:01:00AM |
3 | 4 | 4 | 3 | 10/1/2019 | 09:02:00AM |
3 | 4 | 4 | 4 | 10/1/2019 | 09:03:00AM |
3 | 4 | 4 | 5 | 10/1/2019 | 09:04:00AM |
3 | 4 | 4 | 6 | 10/1/2019 | 09:05:00AM |
If anyone has experienced this issue or knows how to fix it, any help would be very appreciated.
Solved! Go to Solution.
@phodges ,
You may modify your measure using dax like pattern below:
Begin Time Current Event =
VAR Currentevent =
MAX ( 'Sheet1'[Event ID] )
VAR Currenteventbeginningtime =
CALCULATE (
MIN ( 'Sheet1'[Date and Time] ),
FILTER ( 'Sheet1', 'Sheet1'[Event ID] = Currentevent )
)
RETURN
Currenteventbeginningtime
End Time Previous Event =
VAR Previousevent =
MAX ( 'Sheet1'[Event ID] ) - 1
VAR Previouseventendingtime =
CALCULATE (
MAX ( 'Sheet1'[Date and Time] ),
FILTER ( 'Sheet1', 'Sheet1'[Event ID] = Previousevent )
)
RETURN
Previouseventendingtime
Regards,
Jimmy Tao
Thank you Jimmy! I was actually working on it some more yesterday and the DAX below made it work:
@phodges, You need to move out the final formula from var and try like this
Begin Time Current Event =
Currenteventbeginningtime = CALCULATE(MIN('Sheet1'[Date and Time]), 'Sheet1'[Event ID] = max('Sheet1'[Event ID]))
End Time Previous Event =
Previouseventendingtime= CALCULATE(MAX('Sheet1'[Date and Time]), 'Sheet1'[Event ID] = max('Sheet1'[Event ID]) - 1)
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Thank you so much for your reply, and I am sorry for my very late reply. I did not receive a notification that you had replied. There is some sample data at the beginning of this forum post, but the solution has already been found. Either solution, the one @v-yuta-msft provided or the one I provided works, though for the earlier Test ID still needs to be added as a filter. I think that was my main problem, not including Test ID as a filter. But thank you for the help! I very much appreciate you reaching out and for being willing to help.
@phodges , Thanks for the update. Please accept the solution that has worked for you.
@phodges ,
You may modify your measure using dax like pattern below:
Begin Time Current Event =
VAR Currentevent =
MAX ( 'Sheet1'[Event ID] )
VAR Currenteventbeginningtime =
CALCULATE (
MIN ( 'Sheet1'[Date and Time] ),
FILTER ( 'Sheet1', 'Sheet1'[Event ID] = Currentevent )
)
RETURN
Currenteventbeginningtime
End Time Previous Event =
VAR Previousevent =
MAX ( 'Sheet1'[Event ID] ) - 1
VAR Previouseventendingtime =
CALCULATE (
MAX ( 'Sheet1'[Date and Time] ),
FILTER ( 'Sheet1', 'Sheet1'[Event ID] = Previousevent )
)
RETURN
Previouseventendingtime
Regards,
Jimmy Tao
Hello again Jimmy,
Sorry, but I have to clarify an error. The Event ID in the measures above should be Event ID Raw, not just Event ID. The slicer I use is also Event ID Raw. I apologize for that confusion, but thank you again for looking into this.
Hello Jimmy,
Thank you for your response. I am trying to get the beginning time of a current Event ID within a specific Test ID and am using the MIN() function to get it. I also want to get the ending time of the previous Event ID (from the same Test ID) and am using the MAX() function to get it. However, for some Test IDs, I get the opposite, I use MIN() to get the beginning time of the current Event ID and instead get the ending time of the current Event ID, and I use MAX() to get the ending time of the previous Event ID and instead get the beginning time of the previous Event ID. This only happens for some of the Test IDs, meaning for all Event IDs in a specific Test ID the times show backwards as decribed above. For other Test IDs, everything works as expected.
Here is the measure I use to get the beginning time of the current Event ID:
Here is the measure I use to get the ending time of the previous Event ID:
@phodges ,
"However, for some of the Test IDs, the event beginning and end times are showing backwards, i.e. what should be the min time is showing as the max time and vice versa."
I'm confused on this description, could you please clarify more details on your issue?
Regards,
Jimmy Tao
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |