FILTER and COUNTROWS, need help to understand formula

Anonymous — Fri, 12 Jun 2020 20:28:46 GMT

Hi,

I have a formula that seems to work (assigns negative deltas to negatives, positive deltas to positives), but I can't really understand it, thus not sure if it's possible to improve it.

My_Measure =
CALCULATE (
    [Delta],
    FILTER (
        VALUES ( Products[Items] ),
        COUNTROWS (
            FILTER ( Ranges, [Delta] >= Ranges[Negative] && [Delta] < Ranges[Positive] )
        ) > 0
    )
)

Ranges is a simple table like this:

Range	Negative	Positive
Negative	-999999999	0.00001
Positive	-0.00001	999999999

Not sure if this table is necessary, however this not my setup, so I don't want to break it, if I'm not sure 100%.

Re: FILTER and COUNTROWS, need help to understand formula

lbendlin — Fri, 12 Jun 2020 23:01:11 GMT

Use DAX Studio (install it if you don't have it) and check the performance of that measure in various scenarios of your dataset. That will show you if you have to optimize it or not.

topic FILTER and COUNTROWS, need help to understand formula in DAX Commands and Tips

FILTER and COUNTROWS, need help to understand formula

Re: FILTER and COUNTROWS, need help to understand formula