topic Re: Regression Formula in DAX Commands and Tips

Regression Formula

james_pease — Wed, 31 Aug 2022 19:22:45 GMT

Hello all, I need help with the following error:

The syntax for ',' is incorrect. (DAX(VAR KnownX = AllSelected( 'All_Actual_Sales (3)', 'All_Actual_Sales (3)'[LN(x)])VAR KnownY = ALLSELECTED ( 'All_Actual_Expenses (3)', 'All_Actual_Expenses (3)'[Cost % Column 2])VAR Count_Items = COUNTROWS ( KnownX )VAR Sum_X = CALCULATE(sumx, KnownX)VAR Sum_X2 = CALCULATE(sumx,(KnownX) ^ 2 )VAR Sum_Y = CALCULATE(sumx, KnownY)VAR Sum_XY = Calculate(SUMX, KnownX * KnownY )VAR Average_X = Calculate(AVERAGEX, KnownX )VAR Average_Y = Calculate(AVERAGEX, KnownY )VAR Slope = DIVIDE ( Count_Items * Sum_XY - Sum_X * Sum_Y, Count_Items * Sum_X2 - Sum_X ^ 2 )VAR Intercept = Average_Y - Slope * Average_XRETURN Intercept + Slope * KnownX)).

Here is my DAX formula:

Logarithmic regression =

VAR KnownX = ALLSELECTED ( 'All_Actual_Sales (3)', 'All_Actual_Sales (3)'[LN(x)])

VAR KnownY = ALLSELECTED ( 'All_Actual_Expenses (3)', 'All_Actual_Expenses (3)'[Cost % Column 2])

VAR Count_Items =

COUNTROWS ( KnownX )

VAR Sum_X =

CALCULATE(sumx, KnownX)

VAR Sum_X2 =

CALCULATE(sumx,(KnownX) ^ 2 )

VAR Sum_Y =

CALCULATE(sumx, KnownY)

VAR Sum_XY =

Calculate(SUMX, KnownX * KnownY )

VAR Average_X =

Calculate(AVERAGEX, KnownX )

VAR Average_Y =

Calculate(AVERAGEX, KnownY )

VAR Slope =

DIVIDE (

Count_Items * Sum_XY - Sum_X * Sum_Y,

Count_Items * Sum_X2 - Sum_X ^ 2

)

VAR Intercept =

Average_Y - Slope * Average_X

RETURN

Intercept + Slope * KnownX

Thank you in advance. I used this website as a reference https://xxlbi.com/blog/simple-linear-regression-in-dax/
In his example, he used columns from 1 table. My data for expenses and sales are in two separate tables. I think that is where my issue stems from.

Re: Regression Formula

daXtreme — Wed, 31 Aug 2022 21:33:15 GMT

Just check your syntax. You can try this formatter: www.daxformatter.com.

The line that is incorrect:

VAR KnownX = ALLSELECTED ( 'All_Actual_Sales (3)', 'All_Actual_Sales (3)'[LN(x)])

The error the formatter reports is the comma ofter the first occurence of 'All_Actual_Sales (3)'. Please check the syntax of ALLSELECTED. You cannot put a table and then, after a comma, a column under ALLSELECTED. You can put in there either a table name, or a (set of) column(s) or nothing at all. You are trying to mix things incorrectly.

Re: Regression Formula

james_pease — Wed, 31 Aug 2022 21:59:02 GMT

Thank you, so what expression would I use if I a simply trying to say, KnownX is column LN(x) for each row?Distinct?

Re: Regression Formula

james_pease — Wed, 31 Aug 2022 23:37:11 GMT

Ok so I think I have most if it right except the selectcolumns again,

Logarithmic regression =

VAR Known =

FILTER (

SELECTCOLUMNS (

ALLSELECTED ( 'All_Actual_Sales (2)'[Actual Sales Null Values], 'All_Actual_Expenses (2)'[Cost % Column] ),

"Known[X]", [Actual Sales Null Values],

"Known[Y]", [Cost % Column]

AND (

NOT ( ISBLANK ( Known[X] ) ),

NOT ( ISBLANK ( Known[Y] ) )

)

VAR Count_Items =

COUNTROWS ( Known )

VAR Sum_X =

SUMX ( Known, Known[X] )

VAR Sum_X2 =

SUMX ( Known, Known[X] ^ 2 )

VAR Sum_Y =

SUMX ( Known, Known[Y] )

VAR Sum_XY =

SUMX ( Known, Known[X] * Known[Y] )

VAR Average_X =

AVERAGEX ( Known, Known[X] )

VAR Average_Y =

AVERAGEX ( Known, Known[Y] )

VAR Slope =

DIVIDE (

Count_Items * Sum_XY - Sum_X * Sum_Y,

Count_Items * Sum_X2 - Sum_X ^ 2

)

VAR Intercept =

Average_Y - Slope * Average_X

RETURN

SumX(

Distinct (All_Actual_Sales[Actual Sales Null Values]),

Intercept + Slope * All_Actual_Sales[Actual Sales Null Values]

)

Error: All column arguments of the ALL/ALLNOBLANKROW/ALLSELECTED/REMOVEFILTERS function must be from the same table.

In this case do I simply remove ALLSELECTED?

Re: Regression Formula

daXtreme — Thu, 01 Sep 2022 03:34:57 GMT

Can't give any advice since I don't know what the model looks like.

Re: Regression Formula

james_pease — Thu, 01 Sep 2022 15:29:44 GMT

The dificulty is that the columns I am using are from 2 diffrent tables. I have tried to use power query to merge the tables but becuase they are so large, power query consitently crashes. (3 m rows). So Im trying to write a regression formula using "LN(x)" which is a calculated column and "cost % column" which is another calculated column.

Re: Regression Formula

daXtreme — Fri, 02 Sep 2022 07:46:46 GMT

Power Query can be written in an optimized manner as well, just like DAX. Not all DAX is performant as well as not all M is. One has to investigate how to write it to make it fast. 3M rows does not seem to be an overly excessive figure for M not to be able to handle this gracefully and quickly. I think your code is just.... suboptimal. Please, for any M optimizations consult the blog of Chris Webb, Chris Webb's BI Blog: Power Query Chris Webb's BI Blog (crossjoin.co.uk). Or, alternatively, you could ask a question about it on the PBI forum dedicated to Power Query/M.