Why is this DAX not working?

[Anonymous]

Good morning everyone!

Firstly I will explain you the exercise:

I would like to count the number of people who must fulfil two different conditions and the result of that count, must be shown in a reverse cumulative total in a chart.

This is the data I'm workin with: 

And this is how is linked to the calendar table:

As you can appreciate is linked by an inactive relationship (DATE column from 'Tabla Calendario' to FECHA CORREGIDA from 'DETALLES EMPLEADOS NT')

And this is the dax I'm using to count the number of employees:

Part 1 is the part I'm using to count the employees if they meet the conditions described below

Part 2 is the part I'm using to active the relationship between the two tables and create the reverse cumulative total that must be shown in the chart

test1 =

VAR FINDE  = SELECTEDVALUE('TABLA CALENDARIO'[Date])

RETURN

CALCULATE( CALCULATE( 

Part 1:  COUNTROWS(FILTER('DETALLES EMPLEADOS NT' ;

'DETALLES EMPLEADOS NT'[GRUPO LABORAL]="05" && (('DETALLES EMPLEADOS NT'[FECHA CORREGIDA] > FINDE ) || (('DETALLES EMPLEADOS NT'[FECHA CORREGIDA] < FINDE ) && ('DETALLES EMPLEADOS NT'[FIN CUPO] < FINDE )

                                                                                                                                 )

                                                                                                                              )

                                                                                                                          )

                                                                                                                      ) ;

Part 2: USERELATIONSHIP('DETALLES EMPLEADOS NT'[FECHA CORREGIDA];'TABLA CALENDARIO'[Date]);

                    FILTER(

                            ALLEXCEPT('TABLA CALENDARIO';'TABLA CALENDARIO'[Date];'TABLA CALENDARIO'[Year]);

                                                 'TABLA CALENDARIO'[Month Year Number] >= MIN('TABLA CALENDARIO'[Month Year Number] )

                                                                                         )

                                                                                  )

                                                                           )

The measure works if I don't select a date but  once I select any date, the chart show nothing

This is the full DAX not broken down:

Test1 = 

VAR FINDE = SELECTEDVALUE('TABLA CALENDARIO'[Date]) RETURN CALCULATE(CALCULATE(COUNTROWS(FILTER('DETALLES EMPLEADOS NT';'DETALLES EMPLEADOS NT'[GRUPO LABORAL]="05" &&

(('DETALLES EMPLEADOS NT'[FECHA CORREGIDA] > FINDE ) || (('DETALLES EMPLEADOS NT'[FECHA CORREGIDA] < FINDE ) && ('DETALLES EMPLEADOS NT'[FIN CUPO] < FINDE)))));USERELATIONSHIP('DETALLES EMPLEADOS NT'[FECHA CORREGIDA];'TABLA CALENDARIO'[Date]);FILTER(ALLEXCEPT('TABLA CALENDARIO';'TABLA CALENDARIO'[Date];'TABLA CALENDARIO'[Year]);'TABLA CALENDARIO'[Month Year Number] >= MIN('TABLA CALENDARIO'[Month Year Number]))))

Many thanks in advance and have a great Monday!

Kind regards,

Adrian

[vanessafvg]

@Anonymous  can you share the data in text format, and give an example of what you expecting?

[Anonymous]

Hi @vanessafvg !!

NAME          //  FECHA CORREGIDA  //  FIN CUPO

PEPE           //      10/01/2020           //   03/07/2020

ANA           //      14/06/2020          //    22/07/2020

JUAN         //      10/09/2020          //    10/03/2021

ADOLFO    //      27/10/2020          //     27/04/2021

PEDRO      //       27/10/2020          //     27/04/2021

And this is what I'm expecting

Taking a look into the conditions for the people to be counted:

FECHA CORREGIDA > DATE SELECTED 

OR

FECHA CORREGIDA < DATE SELECTED > FIN CUPO

If I select any date in the filter (for example 30/04/2020) the measure should count 4 people and the chart should display:

• 4 people from May 1st till June 14th.
• 3 people from June 15th till September 10th.
• 2 people from September 11th till Octiber 27th

Hope you could understand me 

Regards

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create measure like DAX below, and there is no need to create relationship between the two tables.

Measure1 =
VAR FINDE =
    SELECTEDVALUE ( 'TABLA CALENDARIO'[Date] )
RETURN
    CALCULATE (
        COUNT ( 'DETALLES EMPLEADOS NT'[NAME] ),
        FILTER (
            ALLSELECTED ( 'DETALLES EMPLEADOS NT' ),
            'DETALLES EMPLEADOS NT'[FECHA CORREGIDA] > FINDE
                || ( 'DETALLES EMPLEADOS NT'[FECHA CORREGIDA] < FINDE
                && 'DETALLES EMPLEADOS NT'[FIN CUPO] > FINDE )
        )
    )

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai !

Firstly, many thanks for your answer!

Okay the count of the employees which meet the conditions works perfectly but now, what about the reverse cumulative total?

Do I have to introduce DAX below into the measure you have created? Put it up in other measure? .. 

I don't know how to do it and that is what is struggling my head..

If you could resolve the question about the reverse cumulative total the problem will be solved

FILTER(

ALLEXCEPT( 'TABLA CALENDARIO' ; 'TABLA CALENDARIO'[Date] ; 'TABLA CALENDARIO'[Year] ) ;

                                                 'TABLA CALENDARIO'[Month Year Number] >= MIN('TABLA CALENDARIO'[Month Year Number] )

Many thanks in advanced!

Kind regards,

Adrian

[v-xicai]

Hi @Anonymous ,

You don't have to add this part of formula above into the my original formula, just put the 'TABLA CALENDARIO'[Date] into the Axis box of chart visual.

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.