Count how many register from a type are in other table

[Anonymous]

Hi everyone,

Currently we have a database (BD_Registros) where are registered some data from operations. Over this data, there is a column called "Estado" (status) that is getted comparing the input value with some ranges previously defined. 

Now I want to add a counter of how many days keep a variable in some status, specially for those which are "fuera de rango" (out of range) and "Dentro de rango" (in range)/"En objetivo" (in objective). 

I was doing this:

The column called "Dia_FueraRango" recieves the last date that a variable was registered as "Fuera de rango", but I have noted that previously of that date there is also other register event with the same variable also out of range, but it isn't taking this. (Its just taking the last value but I want to take the first date when the variable changes to "Fuera de rango" from other status)

The name of the table from photo is BD_Variables

Thank you in advance,

[v-kelly-msft]

Hi @Anonymous ,

Could you pls provide some sample data for test?Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Kelly,

Sure, let me show you:

As you can see, the variable EL-14 changes its status to "Fuera de rango" on July 16th, but with my code isn't taking this value, instead is taking just the last time that "Fuera de rango" status was setted (for today I mean, July 21st). 

Thank you in advance,

[v-kelly-msft]

Hi  @Anonymous ,

Try to use below expression instead:

Dia_FueraRango=CALCULATE(
MIN(BD_REGISTROS[FECHA REAL].[Date]),
FITER(ALL(BD_REGISTROS),AND((BD_REGISTROS[Estado]="Fuera de rango"),(BD_REGISTROS[Nombre VariableX]=BD_VARIABLES[CodigoX]))))

If this doesnt help,could you pls share a sample .pbix file(can be editable) for test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!