Forum Discussion

james_pease's avatar
james_pease
Helper III
4 years ago

Regression Formula

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.

6 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    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's avatar
      james_pease
      Helper III
      4 years ago

      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's avatar
        daXtreme
        Solution Sage
        4 years ago

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

  • james_pease's avatar
    james_pease
    Helper III
    4 years ago

    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?