Forum Discussion

cgrimes's avatar
cgrimes
Advocate II
2 years ago
Solved

Error MDXScript(model)(201,9) with LINESTX in DAX Formula

I am working on a DAX formula in Power BI to perform a linear regression analysis using the LINESTX function. The goal is to predict next month's sales based on several variables from previous data. ...
Need Help
  • cgrimes's avatar
    cgrimes
    2 years ago

    well, it was a weird fix, it did not like that I was using summarizecolumns, had to change to summarize 

    Next Month Fill2 = 

VAR Fit = LINESTX(
    CALCULATETABLE(
        SUMMARIZE(
            'Date Master',
            'Date Master'[FirstOfMonth],
            "Sales", SUM('Data Sales'[Net Amount (USD)]),
            "WDays", SUM('Date Master'[WorkDay]),
            "RRS", CALCULATE([d day $ RRQR per Workday], 'Measures MMT'[Catagory] = "Sales"),
            "SoMLBL", [start of month latBL]^.5,
            "SoMOD", [StartOfMonthOrdersDue]
        ),
        ALL('Date Master'),
        'Date Master'[FirstOfMonth] >= EOMONTH(MIN(TODAY(),MAX('Date Master'[Date])), -14) + 1,
        'Date Master'[FirstOfMonth] <= EOMONTH(MIN(TODAY(),MAX('Date Master'[Date])), -2) + 1
    )    ,
        [Sales],
        [WDays],
        [RRS],
        [SoMLBL],
        [SoMOD]
    )

VAR Slope1 = SELECTCOLUMNS( Fit , [Slope1])
VAR Slope2 = SELECTCOLUMNS( Fit , [Slope2])
VAR Slope3 = SELECTCOLUMNS( Fit , [Slope3])
VAR Slope4 = SELECTCOLUMNS( Fit , [Slope4])
VAR Intercept = SELECTCOLUMNS( Fit , [Intercept])
VAR x1 = CALCULATE(SUM('Date Master'[WorkDay]))
VAR x2 = CALCULATE(
    [d day $ RRQR per Workday],
    'Measures MMT'[Catagory] = "Sales",
    PREVIOUSMONTH('Date Master'[Date])
)
VAR x3 = [start of month latBL]^.5
VAR x4 = [StartOfMonthOrdersDue]
VAR y = Slope1 * x1 + Slope2 * x2 + Slope3 * x3 + Slope4 * x4 + Intercept

RETURN
y



Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
2 years ago

Hello cgrimes,

 

The SELECTCOLUMNS function returns a table, not a single scalar value. So, when you use:

VAR Slope1 = SELECTCOLUMNS( Fit , [Slope1])

When you use Slope1 * x1 further in your formula, this might be causing an error as you can't multiply a table directly with a scalar value. To get the actual value, you may need to do something like:

VAR Slope1 = SUMX(SELECTCOLUMNS( Fit , [Slope1]), [Slope1])

Should you require any further assistance, please do not hesitate to reach out to me.

cgrimes's avatar
cgrimes
Advocate II
2 years ago

I got the same error,

 

I have a diferent version that was working but behaving unexpectedly when filtered on the date table.

it used the selectcolumn() without issue.

Next Month Fill = 

VAR Fit = LINESTX(
    CALCULATETABLE(
        VALUES('Date Master'[FirstOfMonth]),
        ALLSELECTED('Date Master'),
        'Date Master'[FirstOfMonth] >= EOMONTH(MAX('Date Master'[Date]), -14) + 1,
        'Date Master'[FirstOfMonth] <= EOMONTH(MAX('Date Master'[Date]), -2)+1
       
    ),
    VAR CurrentFirstOfMonth = 'Date Master'[FirstOfMonth] -- Capture the current [FirstOfMonth] value
    RETURN
        CALCULATE(
            SUM('Data Sales'[Net Amount (USD)]),
            ALLSELECTED('Date Master'),
            'Date Master'[FirstOfMonth] = CurrentFirstOfMonth
        ),
    VAR CurrentFirstOfMonth = 'Date Master'[FirstOfMonth] -- Capture the current [FirstOfMonth] value
    RETURN
        CALCULATE(
            SUM('Date Master'[WorkDay]),
            ALLSELECTED('Date Master'),
            'Date Master'[FirstOfMonth] = CurrentFirstOfMonth
        ),
    VAR CurrentFirstOfMonth = EDATE('Date Master'[FirstOfMonth],-1)-- Capture the current [FirstOfMonth] value
    RETURN
        CALCULATE(
            [d day $ RRQR per Workday],
            'Measures MMT'[Catagory] = "Sales",
            ALLSELECTED('Date Master'),
            'Date Master'[FirstOfMonth] = CurrentFirstOfMonth
        ),
    VAR CurrentFirstOfMonth = 'Date Master'[FirstOfMonth] -- Capture the current [FirstOfMonth] value
    RETURN
        CALCULATE(
            [start of month latBL]^.5,
            ALLSELECTED('Date Master'),
            'Date Master'[FirstOfMonth] = CurrentFirstOfMonth
        ),
    VAR CurrentFirstOfMonth = 'Date Master'[FirstOfMonth] -- Capture the current [FirstOfMonth] value
    RETURN
        CALCULATE(
    [StartOfMonthOrdersDue],
            ALLSELECTED('Date Master'),
            'Date Master'[FirstOfMonth] = CurrentFirstOfMonth
        )

)

VAR Slope1 = SELECTCOLUMNS( Fit , [Slope1])
VAR Slope2 = SELECTCOLUMNS( Fit , [Slope2])
VAR Slope3 = SELECTCOLUMNS( Fit , [Slope3])
VAR Slope4 = SELECTCOLUMNS( Fit , [Slope4])
VAR Intercept = SELECTCOLUMNS( Fit , [Intercept])
VAR x1 = CALCULATE(SUM('Date Master'[WorkDay]))
VAR x2 = CALCULATE(
    [d day $ RRQR per Workday],
    'Measures MMT'[Catagory] = "Sales",
    PREVIOUSMONTH('Date Master'[Date])
)
VAR x3 = [start of month latBL]^.5
VAR x4 = [StartOfMonthOrdersDue]
VAR y = Slope1 * x1 + Slope2 * x2 + Slope3 * x3 + Slope4 * x4 + Intercept

RETURN
y





 

  • cgrimes's avatar
    cgrimes
    Advocate II
    2 years ago

    well, it was a weird fix, it did not like that I was using summarizecolumns, had to change to summarize 

    Next Month Fill2 = 

VAR Fit = LINESTX(
    CALCULATETABLE(
        SUMMARIZE(
            'Date Master',
            'Date Master'[FirstOfMonth],
            "Sales", SUM('Data Sales'[Net Amount (USD)]),
            "WDays", SUM('Date Master'[WorkDay]),
            "RRS", CALCULATE([d day $ RRQR per Workday], 'Measures MMT'[Catagory] = "Sales"),
            "SoMLBL", [start of month latBL]^.5,
            "SoMOD", [StartOfMonthOrdersDue]
        ),
        ALL('Date Master'),
        'Date Master'[FirstOfMonth] >= EOMONTH(MIN(TODAY(),MAX('Date Master'[Date])), -14) + 1,
        'Date Master'[FirstOfMonth] <= EOMONTH(MIN(TODAY(),MAX('Date Master'[Date])), -2) + 1
    )    ,
        [Sales],
        [WDays],
        [RRS],
        [SoMLBL],
        [SoMOD]
    )

VAR Slope1 = SELECTCOLUMNS( Fit , [Slope1])
VAR Slope2 = SELECTCOLUMNS( Fit , [Slope2])
VAR Slope3 = SELECTCOLUMNS( Fit , [Slope3])
VAR Slope4 = SELECTCOLUMNS( Fit , [Slope4])
VAR Intercept = SELECTCOLUMNS( Fit , [Intercept])
VAR x1 = CALCULATE(SUM('Date Master'[WorkDay]))
VAR x2 = CALCULATE(
    [d day $ RRQR per Workday],
    'Measures MMT'[Catagory] = "Sales",
    PREVIOUSMONTH('Date Master'[Date])
)
VAR x3 = [start of month latBL]^.5
VAR x4 = [StartOfMonthOrdersDue]
VAR y = Slope1 * x1 + Slope2 * x2 + Slope3 * x3 + Slope4 * x4 + Intercept

RETURN
y