topic Variable strange behavior in a measure, report only if substract a number in DAX Commands and Tips

Variable strange behavior in a measure, report only if substract a number

Pier2 — Tue, 25 Jun 2024 23:23:10 GMT

Hello,

I have a variable in a measure who returns a value only if I put a minus number at its end but nothing otherwise.

I use integer to identify years.

I want to report a trend, minus the year cicled in red.

The formula is the following to be sure to see the entire graphic:

m_CoutCapitauxPropres = VAR varPrem = CALCULATE( MIN(tblEntrepriseDonnees[intAnnee]), USERELATIONSHIP(tblEntreprise[KEntreprise], tblEntrepriseDonnees[FKEntreprise]) )-1 RETURN CALCULATE( SUM(tblEntrepriseDonnees[sglCoutCapitauxPropres]), FILTER( CalendrierAnnuel, CalendrierAnnuel[Année fiscale] > varPrem ), USERELATIONSHIP(tblEntreprise[KEntreprise], tblEntrepriseDonnees[FKEntreprise]) )

Should I simply replace -1 for nothing, zéro or +1, everything vanish.

What's the logic behind that behavior? How to solve it? The best solution, for me would be to replace -1 for nothing.

Re: Variable strange behavior in a measure, report only if substract a number

Anonymous — Wed, 26 Jun 2024 02:03:02 GMT

Hi Pier2 ,

Could you please take this DAX out and create a measure to see whether it returns a null value?

CALCULATE( MIN(tblEntrepriseDonnees[intAnnee]), USERELATIONSHIP(tblEntreprise[KEntreprise], tblEntrepriseDonnees[FKEntreprise]) )

Best Regards,
Dino Tao

Re: Variable strange behavior in a measure, report only if substract a number

Pier2 — Fri, 28 Jun 2024 14:05:07 GMT

Hello Anonymous ,

Thanks for your help.

That formula returns effectively a value, which is the right value in its context, the year 2015. That's something I already verified in different ways.

In the RETURN part of my formula, should I change the variable varPrem for an integer (2015, for exemple) it works perfectly. It's like the variable varPrem doen't convey the value to the RETURN.

On the data side, the table looks like this. The year 2015 (the first of the graph) has no ratio calculated and when its the case, I try to get rid of it because that's meaningless. And I starting to believe it is the source of the problem but I don't know why.

intAnnee (year using integer)	sglCoutCapitauxPropres
2015
2016	0,04
2017	0,05
2018	0,00
2019	0,12