Forum Discussion
Helper IISet the minimum value to zero for a DAX written Trend Line
Hi, I found a great web site that has allow me to create my own trend line with DAX. (link https://xxlbi.com/blog/simple-linear-regression-in-dax/). Below is the version of the formula that I used. What I am hoping to do is add something to the formula that will set the minimum value to 0 (zero) for the 'DetailTable'[% Repaired] column as it seems to be skewing how the bar chart looks (see 3 screen shots that follow the formula below).
Here is the same chart with the same 17 weeks (TopN) filer set but I did change the Y-axis range to be a minimum of zero. This works, but I would prefer that the formula "pre filter" this information.
And here is the same chart with the TopN weeks change to the top 10 weeks which is the first setting where the negative values are are no longer present.
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.
2 Replies
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.
JenWilsonThis seems to have helped with what I was looking for. Thank you for your quick response!