SUMX() and Filter an operation

[Anonymous]

Hi people... I'm having a problem with a DAX formula.

I'm trying to get the absolute difference between two columns ( "HH ASIGNADAS" and "HH EJECUTADAS")

Now, in those cases in which there is no value in the "HH ASIGNADAS" column, said difference should not be made. To achieve this I'm using the following expression (measure):

DIF = SUMX(VALUES(ENTREGABLES[CODIGO ENTREGABLE]), IF(HASONEVALUE(BBDD1[HH ASIGNADAS]),[DIF_ABSOLUTA], BLANK()))

Where:

DIF_ABSOLUTA = ABS(SUM(BBDD1[HH ASIGNADAS])-SUM(HH_ejecutadas_minedoc[Horas_ejecutadas]))

BBDD1[HH ASIGNADAS]) = Hours that were assigned to professionals.

HH_ejecutadas_minedoc[Horas_ejecutadas] = Hours that were executed by professionals

And ENTREGABLES[CODIGO ENTREGABLE] is a column that contains all the codes of the documents on which the professionals work.

Locally the expression is working, but it is not giving me a final result (which is the value I am actually looking for). If in the "IF" condition I leave "BLANK()" for when it is not met, I get "BLANK" as a total sum, and line by line it looks like the image above.

Now, if for example I change the "BLANK()" to "0", the table looks like this:

And the final result gives me zero:

The same thing that happens with zero happens if I put a one for example.

I hope the problem is clear. Any help is welcome.

Thank you very much and greetings

Additional comments: I also tried using the CALCULATE() function but nothing happens

DIF_CALCULATE = SUMX(VALUES(ENTREGABLES[CODIGO ENTREGABLE]), CALCULATE([DIF_ABSOLUTA], BBDD1[HH ASIGNADAS] <> BLANK()))

Directly it is as if it does not apply any filter and does the operation regardless of whether "HH ASIGNADAS" has information or not

In the latter case it doesn't matter if I use "BLANK()" or zero, etc.

The data model:

[AlexisOlson · Accepted solution]

I'd try this and tweak as necessary:

DIF =
SUMX (
    VALUES ( ENTREGABLES[CODIGO ENTREGABLE] ),
    VAR _Asignadas = CALCULATE ( SUM ( BBDD1[HH ASIGNADAS] ) )
    VAR _Ejutadas = CALCULATE ( SUM ( HH_ejecutadas_minedoc[Horas_ejecutadas] ) )
    RETURN
        IF (
            ISBLANK ( _Asignadas ), /*|| ISBLANK ( _Ejutatas ),*/
            BLANK (),
            ABS ( _Asignadas - _Ejutadas )
        )
)

I've included a commented-out part that you can include if you want to check both are nonblank.

[Anonymous]

Can I ask you a question? Why did you use Calculate in this expression and not just SUM?

VAR _Asignadas = CALCULATE ( SUM ( BBDD1[HH ASIGNADAS] ) )

[AlexisOlson]

Context transition. I want the SUM to be calculated within the context of the current ENTREGABLES[CODIGO ENTREGABLE] row of SUMX.

Further reading:

https://www.sqlbi.com/articles/understanding-context-transition/

https://www.sqlbi.com/articles/understanding-context-transition-in-dax/

[Anonymous]

It worked!! Thank you very much!!