Use IF() nested in a SELECT() for a report. What is wrong?

[7_Ideas_Digital]

Hi everyone:

I am creating an employee vacation report and I have a problem with the filters so that the user can select "Date Range" and/or "Service" (optional). Inside the report I have written the following:

<<Start:ORDERBY(
    SELECT(Vacaciones[Id Vacacion],
          IF(ISBLANK([Servicio]),
                 AND([Estado]=”Solicitada”,[Fecha Desde]>=[_THISROW].[Fecha Desde]),
                 AND([Estado]=”Solicitada”,[Fecha Desde]>=[_THISROW].[Fecha Desde],[Id Empleado].[Id Servicio]=[_THISROW].[Servicio])
             )
          ),
[Fecha Desde],TRUE)>>

Case 1: Only Date Range

Case 2: Date Range + Service

For some strange reason, when I choose a "Service", the report comes out perfectly with the date range, but when I leave "Service" blank, it does not return any records, since all the records in the date range should appear.

I've searched for a similar case and I can't find the solution.

Any idea why it's not working?

Thanks 😉

[Steve · Accepted solution]

ORDERBY(
  SELECT(
    Vacaciones[Id Vacacion],
    AND(
      [Estado]=”Solicitada”,
      [Fecha Desde]>=[_THISROW].[Fecha Desde],
      OR(
        ISBLANK([_THISROW].[Servicio]),
        [_THISROW].[Servicio]=[Id Empleado].[Id Servicio]
      )
    )
  ),
  [Fecha Desde],
  TRUE
)

[7_Ideas_Digital]

That is exactly the expected result. Thank you so much Steve. 👍