cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

COUNTROWS WITH MULTIPLE CRITERIA IN DIFFERENT COLUMNS

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): 

 

Captura.PNG

 

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:

Captura3.PNG

  • 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

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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!!
 
 

View solution in original post

4 REPLIES 4
d_gosbell
Super User II
Super User II

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
Not applicable

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

 

 

 


@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
Not applicable

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!!
 
 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors