Using dynamic measures based on slicer

[sergio_alvarez]

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:

• MEASURES: but if I set it as MEASURE_MIN_DATE = MIN(DATES), this value is not updated when I change the values of the slicer, and the value is always the absolute minimum of the table DATES.
• VAR: with the same results that when MEASURES.
• CALCULATED COLUMNS in the COURSE table, depending on the values of DATES, with the same results that when MEASURES.

I do not know how to try it in another way.

[v-ljerr-msft · Accepted 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

[v-ljerr-msft · Accepted 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

[sergio_alvarez]

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.

[v-ljerr-msft]

Hi @sergio_alvarez,

Could you post the measure you're using, so that I can help further investigate on it? 

Regards

[sergio_alvarez]

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.

[v-ljerr-msft · Accepted solution]

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

[sergio_alvarez]

YES!!! IT WORKS!!!

I made a mistake introducing the name of the table. 

Thank you very much!!!