Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to get the number of courses whose date of begining is lower than the first date of an interval of dates, and whose date of finishing is bigger than the last date of the same interval.
The courses are stored in a table (COURSES with the fields ID_COURSE, BEGIN_DATE, FINISH_DATE) that I am importing in Power BI Desktop. The interval of dates is compossed by the list of dates from another table (DATES). The table COURSES is related with the table DATES through the field BEGIN_DATE.
I have to work dinamically with the dates selected in the slicer, (the kind of the slicer is 'BETWEEN') that is working over the table DATES.
I do not know how to calculate o filter the registers of the COURSES table, with the interval composed by the minimum date of the slicer and the maximum date or the slicer (having in account that the date of begining of the COURSE is lower than the first date of the date interval, and that the date of finishing of the COURSE is bigger than the last date of the same interval). I have tried to calculate these two values with:
I do not know how to try it in another way.
Solved! Go to Solution.
Hi @sergio_alvarez,
If I understand you correctly, you should be able to use the formula below to create a new measure to get the number of courses in your scenario.
Measure = VAR minDate = MIN ( DATES[Date] ) VAR maxDate = MAX ( DATES[Date] ) RETURN CALCULATE ( COUNTA ( COURSES[ID_COURSE] ), FILTER ( ALL ( COURSES ), COURSES[BEGIN_DATE] <= minDate && COURSES[FINISH_DATE] <= maxDate ) )
Regards
Hi @sergio_alvarez,
Try the formula below.
CURSOS_INTERVALO = VAR minDate = MIN ( DDS_DM_FECHAS[Fecha de consulta] ) VAR maxDate = MAX ( DDS_DM_FECHAS[Fecha de consulta] ) RETURN CALCULATE ( COUNTA ( DDS_HC_09300000_INF_CURSOS_MATRIC_D[nID_CODIGO_CURSO] ); FILTER ( ALL ( DDS_HC_09300000_INF_CURSOS_MATRIC_D ); DDS_HC_09300000_INF_CURSOS_MATRIC_D[dFECHA_INICIO] <= minDate && DDS_HC_09300000_INF_CURSOS_MATRIC_D[dFECHA_FIN] >= maxDate ) )
Regards
Hi @sergio_alvarez,
If I understand you correctly, you should be able to use the formula below to create a new measure to get the number of courses in your scenario.
Measure = VAR minDate = MIN ( DATES[Date] ) VAR maxDate = MAX ( DATES[Date] ) RETURN CALCULATE ( COUNTA ( COURSES[ID_COURSE] ), FILTER ( ALL ( COURSES ), COURSES[BEGIN_DATE] <= minDate && COURSES[FINISH_DATE] <= maxDate ) )
Regards
Hi @v-ljerr-msft,
Thank you very much for your answer.
I have tried applying your proposal, but the result I obtain is:
"A single value for column 'BEGIN_DATE' in table 'COURSES' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result!"
Thank you again.
Hi @sergio_alvarez,
Could you post the measure you're using, so that I can help further investigate on it?
Regards
Hello,
This is the measure I am using:
CURSOS_INTERVALO =
VAR minDate =
MIN ( DDS_DM_FECHAS[Fecha de consulta] )
VAR maxDate =
MAX ( DDS_DM_FECHAS[Fecha de consulta] )
RETURN
CALCULATE (
COUNTA ( DDS_HC_09300000_INF_CURSOS_MATRIC_D[nID_CODIGO_CURSO] );
FILTER (
ALL ( DDS_HC_09100000_INF_CLIENTES_CENTRO_F );
DDS_HC_09300000_INF_CURSOS_MATRIC_D[dFECHA_INICIO] <= minDate
&& DDS_HC_09300000_INF_CURSOS_MATRIC_D[dFECHA_FIN] >= maxDate
)
)
Thank you in advance @v-ljerr-msft.
Hi @sergio_alvarez,
Try the formula below.
CURSOS_INTERVALO = VAR minDate = MIN ( DDS_DM_FECHAS[Fecha de consulta] ) VAR maxDate = MAX ( DDS_DM_FECHAS[Fecha de consulta] ) RETURN CALCULATE ( COUNTA ( DDS_HC_09300000_INF_CURSOS_MATRIC_D[nID_CODIGO_CURSO] ); FILTER ( ALL ( DDS_HC_09300000_INF_CURSOS_MATRIC_D ); DDS_HC_09300000_INF_CURSOS_MATRIC_D[dFECHA_INICIO] <= minDate && DDS_HC_09300000_INF_CURSOS_MATRIC_D[dFECHA_FIN] >= maxDate ) )
Regards
YES!!! IT WORKS!!!
I made a mistake introducing the name of the table.
Thank you very much!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |