Set the minimum value to zero for a DAX written Trend Line

Hi JenWilson

I understand that you would like to modify your DAX expression to ensure that the '% Repaired' column in your 'DetailTable' does not go below zero.

You can use the "MAX" function in DAX to set a floor value of zero for the "% Repaired" column. Here are suggestions for modifications:

RepairTrend =
VAR Known = FILTER(
    SELECTCOLUMNS(
        ALLSELECTED('Calendar'[WeekDate]),
        "Known[X]", 'Calendar'[WeekDate],
        "Known[Y]", MAX('DetailTable'[% Repaired], 0)  -- This ensures the minimum value is 0
    ),
    AND(NOT(ISBLANK(Known[X])),
    NOT(ISBLANK(Known[Y])))
)
VAR SlopeInterept =
    LINESTX(Known, Known[Y], Known[X])
VAR Slope =
    SELECTCOLUMNS(SlopeInterept, [Slope1])
VAR Intercept =
    SELECTCOLUMNS(SlopeInterept, [Intercept])
RETURN
SUMX(DISTINCT('Calendar'[WeekDate]),
    Intercept + Slope * 'Calendar'[WeekDate])

The "MAX" function is used within the "SELECTCOLUMNS" function to compare the value of "% Repaired" with 0 and return the greater of the two. This ensures that any negative values are replaced with zero before the calculation proceeds. 

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.