COUNTROWS WITH MULTIPLE CRITERIA IN DIFFERENT COLUMNS

[Anonymous]

Good afternoon everyone,

I'm struggling because I can not fin the solution for this problem:

This is my main table named 'DETALLES EMPLEADOS' (I have filtered it to ease the problem): 

Okay so, as you can see there are three columns with dates but we are going to focus in the last two 'NUEVA FECHA CORREGIDA' and 'FIN ULT CUPO'.

I have also created a date table named 'CALENDAR'. This table has got only a column named 'DATE' whose first date is 01/01/2015 and the last one 31/12/2023 because I need to connect the data from my main table to this one to create a date filter in the slide.

The goal of my slide is to create a date filter like the attached below which could count the rows from the main table following the next rules:

• IF THE DATE FROM THE COLUMN 'NUEVA FECHA CORREGIDA' <= MINIMUM DATE SELECTED IN THE FILTER : COUNT
• IF THE DATE FROM THE COLUMN 'NUEVA FECHA CORREGIDA' > MINIMUM DATE SELECTED IN THE FILTER : COUNT
• IF THE DATE FROM THE COLUMN 'FIN ULT CUPO' <= MAXIMUM DATE SELECTED IN THE FILTER : COUNT
• IF THE DATE FROM THE COLUMN 'FIN ULT CUPO' > MAXIMUM DATE SELECTED IN THE FILTER : DO NOT COUNT

Obviously the dates of the filter come from the date table that I've created and connected with the main table previously.

I have tried the following DAX but it's not throwing back the expected result: 

RESULTS 2 =

COUNTROWS(

FILTER('DETALLES EMPLEADO';

'DETALLES EMPLEADO'[FIN ULT CUPO]<=MAX('Calendario Prueba'[Date])&&or('DETALLES EMPLEADO'[NUEVA FECHA CORREGIDA]<=MIN('Calendario Prueba'[Date]);'DETALLES EMPLEADO'[NUEVA FECHA CORREGIDA]>min('Calendario Prueba'[Date]))))

Hope you could understand my problem and help me..

Kind regards,

Adrian

[d_gosbell]

By doing > min date or <= min date your condition on the 'DETALLES EMPLEADO'[NUEVA FECHA CORREGIDA] column will return true all the time, so basically what your expression is evaluating is the following:

RESULTS 2 =

COUNTROWS(

FILTER('DETALLES EMPLEADO';

'DETALLES EMPLEADO'[FIN ULT CUPO]<=MAX('Calendario Prueba'[Date])))

So I think the issue might be with the requirements you've been given

[Anonymous]

Hi @d_gosbell ! 

Firstly, thank you for your answer! 

Yes, I thought the same yesterday because the DAX isn't throwing back the employees whose contract start before the minimum date selected so.. should I maybe try to divide the DAX formula in two parts? I mean:

• First part: [NUEVA FECHA CORREGIDA] >= MIN('CALENDARIO PRUEBA'[DATE]) &&  'DETALLES EMPLEADO'[FIN ULT CUPO]<=MAX('Calendario Prueba'[Date].
• Second Part: [NUEVA FECHA CORREGIDA] < MIN('CALENDARIO PRUEBA'[DATE]) &&  'DETALLES EMPLEADO'[FIN ULT CUPO]<=MAX('Calendario Prueba'[Date].

What do you think?

Regards,

Adrian

[d_gosbell]

---

@Anonymous wrote:

Hi @d_gosbell ! 

so.. should I maybe try to divide the DAX formula in two parts? I mean:

• First part: [NUEVA FECHA CORREGIDA] >= MIN('CALENDARIO PRUEBA'[DATE]) &&  'DETALLES EMPLEADO'[FIN ULT CUPO]<=MAX('Calendario Prueba'[Date].
• Second Part: [NUEVA FECHA CORREGIDA] < MIN('CALENDARIO PRUEBA'[DATE]) &&  'DETALLES EMPLEADO'[FIN ULT CUPO]<=MAX('Calendario Prueba'[Date].

What do you think?

---

I think this will probably produce the same result as the existing measure.

Can you try expressing your question as a set of example data? 

So if the slicer was set to a min date of 1 July 2019 and a max date of 31 August 2019 - Can you show tell us which of the example rows of data below you expect to be counted by adding a yes/no in the included column in the table below? 

NUEVA FECHA CORREGIDA	FIN ULT CUPO	Included?
15 Jul 2019	15 Aug 2019
15 Jun 2019	15 Sep 2019
10 Jun 2019	10 Jul 2019
20 Jul 2019	20 Sep 2019
23 May 2019	23 Jun 2019
25 Sep 2019	25 Oct 2019

[Anonymous · Accepted solution]

Hi again @d_gosbell !

I resolved the problem yesterday

I created two more date tables, one of them contain all the dates from 'NUEVA FECHA CORREGIDA' and the other one the dates from 'FIN ULT CUPO'. Then, I wrote this DAX formula: 

CONTEO =

VAR MINI = SELECTEDVALUE('FECHAS MÍNIMAS'[Date])

VAR MAXI = SELECTEDVALUE('FECHAS MÁXIMAS'[Date])

RETURN

COUNTROWS(FILTER(

'DETALLES EMPLEADOS';'DETALLES EMPLEADOS'[Fin Ult.Cupo]<=MAXI && ('DETALLES EMPLEADOS'[FECHA CORREGIDA]<MINI || 'DETALLES EMPLEADOS'[FECHA CORREGIDA]>=MINI)))

Anyway, many thanks for your help!

Cheers!!