Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help including IF FUNCTION inside a measure.

Hello guys, I'm not sure if what I'm trying to do can be done. Some days ago I got some help here with a measure that wasn't working:

Invoice =
SUMX(
	DISTINCT('Table'[ANUNCIANTE]);
	CALCULATE(
		CALCULATE(         
			IF(
				FIRSTNONBLANK('Table'[AGENCIA_ORIGINAL];1)=FIRSTNONBLANK(Dim_agencia[Agencia];1);
				SUM('Table'[FACTURACION])*-1;
				SUM('Table'[FACTURACION])
				
			);
			FILTER(
				'Table';
				('Table'[AGENCIA_ORIGINAL]=FIRSTNONBLANK(Dim_agencia[Agencia];1) && [AGENCIA_MODIFICADA]<>BLANK())
				|| 'Table'[AGENCIA_MODIFICADA]=FIRSTNONBLANK(Dim_agencia[Agencia];1)
			)
		);
		SAMEPERIODLASTYEAR(DATESYTD(Calendario[Date]))
	)
)


Right now this is working. The thing is every ANUNCIANTE has more than one single value for AGENCIA_ORIGINAL and AGENCIA_MODIFICADA. First I tried with VALUES but it didn't work so I went with FIRSTNONBLANK as I read somewhere that it could work. Of course it works but not the way I was expecting since obviously it only takes in account the first value for every column I use.

For me these shouldn't be so hard for example in SQL, but as for know I'm not been able to achieve this in the measure.
What I would like to do is:

IF(
'Table'[AGENCIA_ORIGINAL ]=Dim_agencia[Agencia]
SUM('Table'[FACTURACION])*-1;
SUM('Table'[FACTURACION])
);


I'm thinking I might do the logic in a new COLUMN not measure and then in a new measure just SUM UP this new column... I'm going to try this but meanwhile if someone can explain me how to do this in a measure or if it can't be done, why.

Thank you!

2 Replies