Forum Discussion
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:
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
- amitchandakSuper User
Anonymous ,
Can you share sample data and sample output.
- v-chuncz-msftCommunity Support