Regression Formula

[james_pease]

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.

[james_pease]

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?

[daXtreme]

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.

[james_pease]

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

[daXtreme]

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

[james_pease]

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.

[daXtreme]

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.