topic Re: SUMX() and Filter an operation in DAX Commands and Tips

SUMX() and Filter an operation

Anonymous — Fri, 01 Mar 2024 15:17:18 GMT

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:

Re: SUMX() and Filter an operation

AlexisOlson — Fri, 01 Mar 2024 17:27:54 GMT

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.

Re: SUMX() and Filter an operation

Anonymous — Mon, 04 Mar 2024 01:30:03 GMT

It worked!! Thank you very much!!

Re: SUMX() and Filter an operation

Anonymous — Mon, 04 Mar 2024 01:36:29 GMT

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

Re: SUMX() and Filter an operation

AlexisOlson — Mon, 04 Mar 2024 21:34:54 GMT

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