Forum Discussion
Create calculated column for start and end date
I am working with the following two tables:
- Data sample table, this is showing data collected with a "stage" identifier as well as the time it was collected.
Time Stage Value1 Value2 5/22/2020 13:01:15 1 5/22/2020 13:02:15 1 5/22/2020 13:03:15 1 5/22/2020 14:01:15 2 5/22/2020 14:02:15 2
- Stage Summary Table, this is showing summaries from the stage (The desired output).
Stage Start End 1 5/22/2020 13:01:15 5/22/2020 13:03:15 2 5/22/2020 14:01:15 5/22/2020 14:02:15
These tables are realted via stage number. For table 2, where I am having issues is calculating the Start and End Time. For start time, it needs to filter data 1 by corresponding stage and find the first time.
I have tried the following DAX expression, but am getting odd results:
Start = CALCULATE(FIRSTDATE(ParameterValues[date].[Date]),USERELATIONSHIP(ParameterValues[stageId],Stages[_id]))
Every row is returning 1/1/2020 12:00:00 AM for start and 12/31/2020 12:00:00 AM for end, but neither of these values are in the table.
Im sure I am making a simple mistake, but have been stuck and cannot get it figured out. Thank you in advance for the help!!
att9728 , Not very clear, Both as a new column in table 2
start Time table 2 = minx(filter(table1,table1[Stage] =table2[Stage]),table1[Time])
End Time table 2 = maxx(filter(table1,table1[Stage] =table2[Stage]),table1[Time])
2 Replies
att9728 , Not very clear, Both as a new column in table 2
start Time table 2 = minx(filter(table1,table1[Stage] =table2[Stage]),table1[Time])
End Time table 2 = maxx(filter(table1,table1[Stage] =table2[Stage]),table1[Time])Thank you for this! This is the answer.
