locked
Recursive First Reverse Sign Value DAX RRS feed

  • Question

  • Hi,

    I have a SSAS 2012 Tabular Cube and I am now stuck with a problem concerning something recursive in DAX.

    Basically I am looking for number of zones jump, provided the sign of the numbers are reverse.

    E.g. in the figure below. Number of zones jump for the figure -47,067 is 1, since there is a positive value in zone 2. If the Figure in Zone2 had been 0 or a negative number and Zone 3 had the same (i.e. 0 or a negative number) and Zone 4 had a positive number, the number of Zones jump would be 3. Is this something that could be done in DAX? 

    

    Monday, June 29, 2015 2:08 PM

Answers

  • So Simon is correct that you can't implement recursive functions in DAX. However it does not mean that you can't achieve the result that you are after. You might be able to do something like find the minimum zone with positive values and the minimum zone with negative values and then find the absolute difference between these.

    http://darren.gosbell.com - please mark correct answers

    Wednesday, July 1, 2015 12:14 PM

All replies

  • Hi SaugatMukherjee,

    According to your description, you want to get the "distance" number which the it reaches reverse sign value. Right?

    In DAX, we only have EARLIER() function which can put the current row into outer loop then do the recursive comparison. However, it will compare the current row with all other rows in table. We can't make it stop when getting the first reverse sign number. So your requirement can't be achieved in DAX.

    Regards,


    Simon Hou
    TechNet Community Support


    Tuesday, June 30, 2015 11:30 AM
  • So Simon is correct that you can't implement recursive functions in DAX. However it does not mean that you can't achieve the result that you are after. You might be able to do something like find the minimum zone with positive values and the minimum zone with negative values and then find the absolute difference between these.

    http://darren.gosbell.com - please mark correct answers

    Wednesday, July 1, 2015 12:14 PM
  • Ok, will try that.Just that I will have to use it in the context of the zone I am in. It should be something the minimum zone greater than the current zone, which has a reverse sign. 
    Monday, July 6, 2015 12:08 PM