Virtual column

[FREDY_ORTEGON]

Hello, I have a table called filter and I have another table of records, this table has a slice which filters depending on the data in the filter table. I’m using a formula that counts the records of that slice filtered like this: count(filtered table[ID]), but this formula keeps counting the records of the original table and not the filtered table. what can be the error ?. the filtered table works perfectly, but when using the formula it has no effect.

[Steve]

What are the actual names of the tables and slice? Are they really filter and filtered table?

Where is your formula used? As the app formula of the virtual column mentioned in the subject of your post? In what table?

What is the row filter expression for the slice?

[FREDY_ORTEGON]

thanks for your prompt response.

The real name of the data table is:“NUEVO REPORTE DE CARTERA”,
the real name of the filter table is: “FILTRO CARTERA” and
the name of the slice is: “REPORTE FILTRADO”

I’m using the formula in a virtual column of the table: “FILTRO CARTERA”

My row filer expression is: and(if(ANY(FILTRO CARTERA[Sub-Aplicacion])="",true,ANY(FILTRO CARTERA[Sub-Aplicacion])=[Sub-Aplicacion]),if(ANY(FILTRO CARTERA[Estado])="",true,ANY(FILTRO CARTERA[Estado])=[Estado]),if(ANY(FILTRO CARTERA[Clase])="",true,ANY(FILTRO CARTERA[Clase])=[Clase]),if(ANY(FILTRO CARTERA[Sexo])="",true,ANY(FILTRO CARTERA[Sexo])=[Sexo]),if(ANY(FILTRO CARTERA[Estado Civil])="",true,ANY(FILTRO CARTERA[Estado Civil])=[Estado Civil]),if(ANY(FILTRO CARTERA[Forma de Pago])="",true,ANY(FILTRO CARTERA[Forma de Pago])=[Forma de Pago]))

the row expression asks if some filter fields are blank “” and if so, it would not have any effect, but if the filter table has any data the slice would filter the records that match that data.

Also, try placing the row expression in the virtual column, but it does not work

[Steve]

Row filter expression reformatted for my use:

AND(
  IF(
    (ANY(FILTRO CARTERA[Sub-Aplicacion]) = ""),
    TRUE,
    (ANY(FILTRO CARTERA[Sub-Aplicacion]) = [Sub-Aplicacion])
  ),
  IF(
    (ANY(FILTRO CARTERA[Estado]) = ""),
    TRUE,
    (ANY(FILTRO CARTERA[Estado]) = [Estado])
  ),
  IF(
    (ANY(FILTRO CARTERA[Clase]) = ""),
    TRUE,
    (ANY(FILTRO CARTERA[Clase]) = [Clase])
  ),
  IF(
    (ANY(FILTRO CARTERA[Sexo]) = ""),
    TRUE,
    (ANY(FILTRO CARTERA[Sexo]) = [Sexo])
  ),
  IF(
    (ANY(FILTRO CARTERA[Estado Civil]) = ""),
    TRUE,
    (ANY(FILTRO CARTERA[Estado Civil]) = [Estado Civil])
  ),
  IF(
    (ANY(FILTRO CARTERA[Forma de Pago]) = ""),
    TRUE,
    (ANY(FILTRO CARTERA[Forma de Pago]) = [Forma de Pago])
  )
)

[Steve]

ISBLANK(...) instead of (ANY(...) = ""):

AND(
  IF(
    ISBLANK(FILTRO CARTERA[Sub-Aplicacion]),
    TRUE,
    (ANY(FILTRO CARTERA[Sub-Aplicacion]) = [Sub-Aplicacion])
  ),
  IF(
    ISBLANK(FILTRO CARTERA[Estado]),
    TRUE,
    (ANY(FILTRO CARTERA[Estado]) = [Estado])
  ),
  IF(
    ISBLANK(FILTRO CARTERA[Clase]),
    TRUE,
    (ANY(FILTRO CARTERA[Clase]) = [Clase])
  ),
  IF(
    ISBLANK(FILTRO CARTERA[Sexo]),
    TRUE,
    (ANY(FILTRO CARTERA[Sexo]) = [Sexo])
  ),
  IF(
    ISBLANK(FILTRO CARTERA[Estado Civil]),
    TRUE,
    (ANY(FILTRO CARTERA[Estado Civil]) = [Estado Civil])
  ),
  IF(
    ISBLANK(FILTRO CARTERA[Forma de Pago]),
    TRUE,
    (ANY(FILTRO CARTERA[Forma de Pago]) = [Forma de Pago])
  )
)

[Steve]

OR(..., ...) instead of IF(..., TRUE, ...):

AND(
  OR(
    ISBLANK(FILTRO CARTERA[Sub-Aplicacion]),
    (ANY(FILTRO CARTERA[Sub-Aplicacion]) = [Sub-Aplicacion])
  ),
  OR(
    ISBLANK(FILTRO CARTERA[Estado]),
    (ANY(FILTRO CARTERA[Estado]) = [Estado])
  ),
  OR(
    ISBLANK(FILTRO CARTERA[Clase]),
    (ANY(FILTRO CARTERA[Clase]) = [Clase])
  ),
  OR(
    ISBLANK(FILTRO CARTERA[Sexo]),
    (ANY(FILTRO CARTERA[Sexo]) = [Sexo])
  ),
  OR(
    ISBLANK(FILTRO CARTERA[Estado Civil]),
    (ANY(FILTRO CARTERA[Estado Civil]) = [Estado Civil])
  ),
  OR(
    ISBLANK(FILTRO CARTERA[Forma de Pago]),
    (ANY(FILTRO CARTERA[Forma de Pago]) = [Forma de Pago])
  )
)

[Steve]

IN(..2, ..1) instead of (ANY(..1) = ..2)

AND(
  OR(
    ISBLANK(FILTRO CARTERA[Sub-Aplicacion]),
    IN([Sub-Aplicacion], FILTRO CARTERA[Sub-Aplicacion])
  ),
  OR(
    ISBLANK(FILTRO CARTERA[Estado]),
    IN([Estado], FILTRO CARTERA[Estado])
  ),
  OR(
    ISBLANK(FILTRO CARTERA[Clase]),
    IN([Clase], FILTRO CARTERA[Clase])
  ),
  OR(
    ISBLANK(FILTRO CARTERA[Sexo]),
    IN([Sexo], FILTRO CARTERA[Sexo])
  ),
  OR(
    ISBLANK(FILTRO CARTERA[Estado Civil]),
    IN([Estado Civil], FILTRO CARTERA[Estado Civil])
  ),
  OR(
    ISBLANK(FILTRO CARTERA[Forma de Pago]),
    IN([Forma de Pago], FILTRO CARTERA[Forma de Pago])
  )
)

[Steve · Accepted solution]

It appears you’re attempting your count in the app formula for the TOTAL REGISTROS virtual column of the FILTRO CARTERA slice. The expression, count(REPORTE FILTRADO[Numero de Prestamo]), references the REPORTE FILTRADO slices, which itself makes use of the FILTRO CARTERA slice. This is a circular reference: you’re asking FILTRO CARTERA to count rows of a slice that won’t be established until FILTRO CARTERA has been applied. FILTRO CARTERA has to be completely computed before it can be used. When TOTAL REGISTROS is computed, REPORTE FILTRADO has not yet been filtered because FILTRO CARTERA is itself still being computed.

My recommendation is to move TOTAL REGISTROS to a table that isn’t involved in the generation of REPORTE FILTRADO.

[FREDY_ORTEGON]

WOW that’s great!, i used the last code you sent and create a new table to see the “TOTAL REGISTROS” in fact my application works faster. I’m really grateful for your help. This community is the best! thank you very very much.

I would like to learn how to optimize the functions of appsheet