Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
james_pease
Helper III
Helper III

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 6
james_pease
Helper III
Helper III

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
Solution Sage
Solution Sage

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.

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

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

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.

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.