Slice filtering using multiple row conditions by user input not working

[DanielGT]

Hello friends....

I have a table "Orden trabajo", Ive made a slice to filter this table, I use a different table "Filtro", in this table I use 4 conditions : [Cliente], [Fecha Inicio],  [Fecha Fin],  [Descripcion]

which a user can fill to help filter table " Orden trabajo".

I have this expression in row filter condition for the slice:

OR(
IN(filtro[Cliente],list([Cliente_ID])),
contains([Related OT Items][item],Filtro[Descripcion]),
IN([fecha], SELECT(orden trabajo[Fecha],AND([Fecha] >=ANY(Filtro[Fecha inicio]),[Fecha] <=ANY(Filtro[Fecha Fin]))
)))

but, when I test this expression it does filter accordingly to those rows which were filled by user.

it is my understanding that OR() should provide results whether any conditions are provide 1,2,3 or 4.

I have tested every condition by itself, and they work alone, it is when grouped in the OR() function that the hole expression failed, but if I change the function to AND() the expression works, but only if  user provide the 4 conditions, and what I want is to provide results whether user provide 1,2,3 or 4 conditions.

Ive tried to search for some clarity where is that Im falling. Im not a programmer Im just reading, experimenting and learning by practice.

Ive appreciate any help.

Thanks.

[Joseph_Seddik]

OR(
  [Cliente_ID] = ANY(Filtro[Cliente]),
  IN( ANY(Filtro[Descripción]), [Related OT Items][item] ), 
  AND(
    [Fecha] >= ANY(Filtro[Fecha inicio]),
    [Fecha] <= ANY(Filtro[Fecha Fin])
  )
)

[DanielGT]

@Joseph_Seddik  thanks for your reply, but it doesnt work either  ☹️,  as you can see (pic below) even with one single conditions filled, no results from the slice.😢

[Joseph_Seddik]

Please show the setup of your tables and columns from the app editor. Thanks.

[DanielGT]

This is table "Orden trabajo"

This the table "Filtro"

this is the Slice to table "Orden trabajo"

is this info ok?

[Joseph_Seddik]

Where is the column Cantidad in your expression?

Why do you have two Ordenes Filtradas views in your dashboard view? Are you sure you have at least one of them linked to your slice?

[DanielGT]

"Cantidad" it is to come next if I can get the expression work with the previous records work, the general Idea is to filter the records using one or some inputs from user...

I have two views from the same slice, to see which one works better (one is table view, and the other is card), cause when searching for records the picture is also very useful to located a record. since both are from the same slice the are linked to the table "filtro" which serves to filter "Orden trabajo" and its child records, I hope this makes sense

thanks

[Joseph_Seddik]

Well, the expression should work, otherwise I wouldn't know why. You might want to verify your data and see they really contain the desired values.

Also please make sure you use the expression in your slice. I've just noticed in your screenshot that you are using another expression rather than the one I gave you. 

[DanielGT]

@Joseph_Seddik 

I appreciate your support, after testing and trying different combinations, I've came to realize  that the evaluated conditions in OR(), are not mutually inclusive (i hope its the right term), which means that the filtered slice would be presented with records that meet every condition in the OR(), what I wanted was a expression that could filter the slice using whatever condition the user would provide, that would mean a combination 1 or 2, 1 or 3, 2 or 3, etc. and those conditions would be mutually inclusive, that means using AND(), but in my limited programing knowledge I'm not capable of producing such expression.

So I ended using a this approach of using a expression that would evaluate which conditions are available and then filter the slice accordingly, at least this provide results, sadly I cannot increase the number of conditions for the user to provide, cause this would make me create a more complex combinations to include in the expression.

IFS(
      isblank(filtro[Cliente]),
      and([Fecha]>=any(filtro[Fecha inicio]), [Fecha]<=any(filtro[Fecha Fin]),
      contains([Related OT Items][item],filtro[Descripcion])),
isblank(filtro[Descripcion]),
     and( [Cliente_ID]=any(filtro[Cliente]),
      [Fecha]>=any(filtro[Fecha inicio]), [Fecha]<=any(filtro[Fecha Fin])))

Once again, thansk for your support, I learned from your suggestions

I hope can still count on you support in the future

Daniel

[Joseph_Seddik]

Hi Daniel,

Think about it like this. The slice row filter condition tries to answer a simple question for each row in the table.

The question is: Should I include this particular table row in the slice or discard it? 

The answer for this question comes from the Row Filter Expression. This expression has to give either one of two answers, for each row:

1. true --> include this row
2. false --> discard this row

This is how the slice is built, including or discarding rows from the original table.

The OR() expression will give a true answer if only one of the conditions is met. For example, the Client ID is the same as the Client ID in the Filtro. Therefore, when you specify a date range for example, it will still show you all rows for this client, because it requires only one condition to be true. 

If you want to include the rows that fulfil all conditions, then you can use the following expression for example:

AND(
  OR([Cliente_ID] = ANY(Filtro[Cliente]), ISBLANK(Filtro[Cliente])),
  OR(
    IN( ANY(Filtro[Descripción]), [Related OT Items][item] ), 
    ISBLANK(Filtro[Descripción])
  ),
  AND(
    OR([Fecha] >= ANY(Filtro[Fecha inicio]), ISBLANK(Filtro[Fecha inicio])),
    OR([Fecha] <= ANY(Filtro[Fecha Fin]), ISBLANK(Filtro[Fecha Fin]))
  )
)

[DanielGT]

Joseph

I've finally found a great solutions, there this guy in youtube, @MultitechVisions, you can see this video where among other interesting things, you'll find a nice expression for the filter.

I ended up with this

and(
if(isnotblank(Filtro Entregas[PRODUCTO]),
in([PRODUCTO],Filtro Entregas[producto]),TRUE),

if(isnotblank(Filtro Entregas[Fecha Ini]),[FECHA]>= any(Filtro Entregas[Fecha Ini]),true),
if(isnotblank(Filtro Entregas[Fecha Fin]),[FECHA]<= any(Filtro Entregas[Fecha Fin]),true))

this works great and you can easily increase the number of user conditions.

I hope this would help everybody.

thanks for you support.

daniel

https://www.youtube.com/watch?v=WJuy-UMXvo0