Forum Discussion
Index match, calculation with multiple option filter
Hi Team,
I am new to Power BI and so far has spent lots of time search relevent answer for my analysis.
I am always an excel users, but due to the size of my new datasets (>1GB), Power BI appear to be a better option.
Okay let me get this straight: I would like to calculate the duration of the activities taking place for each participants, but I only have data of specific milestones of each participants as rows. How should I proceeed?
My table
| Participant | Milestone | Date |
| 1011123 | Start | 1989/1/1 |
| 1011198 | start1 | 1988/1/1 |
| 1011167 | End | 2000/2/1 |
| 1011123 | end1 | 2002/3/1 |
| 1011198 | end | 2011/1/1 |
| 1011167 | start | 1989/1/1 |
| 1011123 | pause | 1998/1/1 |
My expected results
| Participants | Start time | End time | Duration |
| 1011123 | 1989/1/1 | 2002/1/1 | 13 year |
| 1011167 | 1989/1/1 | 2000/1/1 | 11 year |
| 1011198 | 1988/1/1 | 2011/1/1 | 23 year |
My thoughts (in excel logic):
1. Create additional column that converts "Start", "start" and "start1" as "start" (and for "end" as well)
2. Indexmatch the time for each participant in start time and end time columns.
3. Calculate the duration.
I was stopped at the first step, I can't really create a new table with filter that includes all the "start" related status. (OR and || didnt work unforutnately)
Happy to see how I could proceed, thank you.
3 Replies
NaveenGandhiMemorable Member
Hello Anonymous2023
Try the below dax to create a new table.
Milestone_Table =
ADDCOLUMNS (
SUMMARIZE (
'Table (2)',
'Table (2)'[Participant],
"Start_Time",
CALCULATE (
MIN ( 'Table (2)'[Date] ),
'Table (2)'[Milestone] = "start"
|| 'Table (2)'[Milestone] = "Start"
|| 'Table (2)'[Milestone] = "start1"
),
"End_Time",
CALCULATE (
MIN ( 'Table (2)'[Date] ),
'Table (2)'[Milestone] = "end"
|| 'Table (2)'[Milestone] = "End"
|| 'Table (2)'[Milestone] = "end1"
)
),
"Duration", DATEDIFF ( [Start_Time], [End_Time], YEAR )
)
Let me know if you have any questions.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate your kudos!!Hi Thank you for your answer.
Just a quick follow-up.
In the original dataset, there is some milestone that is before the start date, which means that MIN does not apply. (I did not include that here for simplicity.)
How should I proceed with this situation?
Thank you.- NaveenGandhi
Is that milestone a different category? If yes you can keep aggregating them as seperate columns same as how i have created start time and end time in the above measure.
Regards,
Naveen
