topic Calc Item to return measures multipled by -1 in DAX Commands and Tips

Calc Item to return measures multipled by -1

Anonymous — Mon, 02 Sep 2024 22:13:57 GMT

I'm creating a calculation item that, when applied as a filter, flips the negative or positive signs of a number to the opposite.

e.g., if a cost is -5000, using this calc item will make it 5000.

So far I am using:

IF(CONTAINSSTRING (SELECTEDMEASURENAME (), "Cost") || IF(CONTAINSSTRING (SELECTEDMEASURENAME (), "Revenue") ,
SELECTEDMEASURE () * -1,
SELECTEDMEASURE ())

This works great if the measure itself contains the string "Cost" or "Revenue" in it. I'd like to know if there is a means of achieving the above without doing a string match, but instead checking the underlying DAX of a measure and then computing from there.

e.g., measure is "Entertainment Cost", which can be calculated by Cost filtered by Entertainment.

CALCULATE([Cost],
Dim[Cost Type] = "Entertainment")

How can I check if the measure itself is referring to either the dependent "cost" measure or column above and then doing the flipping of the negative/positive sign.

Thanks.

Re: Calc Item to return measures multipled by -1

OwenAuger — Mon, 02 Sep 2024 23:48:04 GMT

Hi Anonymous

A calculation item can't reference either the DAX code or the measure dependencies of SELECTEDMEASURE ().

If you need to selectively apply a calculation item to measures within DAX expressions, so that the calculation item is applied to either [Cost] or [Revenue] within DAX expressions, I suggest following the method in this article:

https://www.sqlbi.com/articles/using-calculation-groups-to-selectively-replace-measures-in-dax-expressions/

Note: The INFO.CALCDEPENDENCY function does return dependency information and can be used when querying the model, but cannot be used in measure, calculated column or calculated table definitions.