Make a sum grouped by ID

[Anonymous]

Hello there,

I have this table down below. I have been asked to show in a visual the total time of each ID but just the ones who managed to get to the Status "Working" (in this case the IDs 2 and 3). I know this can be done using some kind of "Filter" function variation, but I can't seem to work it out.

ID	Status	Time
1	Installing	3s
1	Downloading	6s
2	Installing	2s
2	Downloading	4s
2	Working	5s
3	Installing	2s
3	Downloading	2s
3	Working	3s
4	Installing	1s
4	Downloading	5s

Any help is gladly appreciated!

Thanks for your time and patience,

[ryan_mayu · Accepted solution]

@Anonymous 

maybe you can try to create a column

Column = 
VAR _check=maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Status]="Working"),'Table'[Status])
return if(_check="","N","Y")

and filter by the new column

[Anonymous]

Hello Ryan,

In fact, that is a really good way to solve it. But I've come up with some problems:

Measure =

VAR _check=maxx(FILTER('comms historico_estados','comms historico_estados'[nrf_nrf_id]=EARLIER('comms historico_estados'[nrf_nrf_id])&&'comms historico_estados'[estado_estado_id]=13&&'comms historico_estados'[estado_estado_id]=10),'comms historico_estados'[estado_estado_id])

return if(_check<>13 && _check<>10,"N","Y")

When I made the first post I gave a modified example of what I am actually working with to simplify it, but this measure is a more precise one. In this measure I've taken what you told me and tried to adapt it, but I couldn't. To be more specific, the "working" status is now a number (13) and I need it to work with two conditions: I only want the IDs which managed to get the "working" status (13) or the IDs which managed to get the imaginary status "Done" (number 10) without having passed through the "working" status.

I know it can be quite complicated to understand because my english is not perfect, but if you need to ask anything please feel free! I'll give as many details as I can

Thank you so much for your time and patience Ryan,

Regards

[ryan_mayu]

@Anonymous 

What I created is a column, not a measure. 

maybe you can try this

column=

VAR _check=maxx(FILTER('comms historico_estados','comms historico_estados'[nrf_nrf_id]=EARLIER('comms historico_estados'[nrf_nrf_id])&&('comms historico_estados'[estado_estado_id]=13||'comms historico_estados'[estado_estado_id]=10)),'comms historico_estados'[estado_estado_id])
return if(_check="","N","Y")

[Anonymous]

The "" part in the return clause gives me an error, I imagine because it's trying to read a string in numbers, right? What should I put instead?

[ryan_mayu]

you can try this

column=

VAR _check=maxx(FILTER('comms historico_estados','comms historico_estados'[nrf_nrf_id]=EARLIER('comms historico_estados'[nrf_nrf_id])&&('comms historico_estados'[estado_estado_id]=13||'comms historico_estados'[estado_estado_id]=10)),'comms historico_estados'[estado_estado_id])
return if(isblank(_check),"N","Y")

[Anonymous]

Finalizados/En Garantía = 
VAR _check13=maxx(FILTER('comms historico_estados','comms historico_estados'[nrf_nrf_id]=
EARLIER('comms historico_estados'[nrf_nrf_id])&&'comms historico_estados'[Grupo Estado]="En Garantía"),'comms historico_estados'[Grupo Estado]) 

VAR _check10=maxx(FILTER('comms historico_estados','comms historico_estados'[nrf_nrf_id]=
EARLIER('comms historico_estados'[nrf_nrf_id])&&'comms historico_estados'[Grupo Estado]="Finalizado"),'comms historico_estados'[Grupo Estado]) 

return if(_check13<>BLANK(),"En Garantía",IF(_check10<>BLANK(), "Finalizado", "N"))

 Hello Ryan,

With some adaptations I finally managed to get it right. In the end I made it to show "Finalizado" only when the status "En garantía" isn't shown in that ID, which is what I was looking for in the beginning. You don't know how helpful you've been!

Thank you so much

[ryan_mayu]

you are welcome

[Hariharan_R]

Hi @Anonymous 

We can achieve this result in different ways. Added one option

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin

[Anonymous]

Hello Hari!

First, thank you so much for your time.

See the thing is, in the table you end up getting there isn't a sum of all the seconds of every phase the IDs 2 and 3 pass. The ideal would be having the table you have but in the "Time" column it would appear "11s" for ID 2 and "7s" for ID 3