Forum Discussion
MdxScript(Model) (20,1) calculation error
It's important to note that certain functions, like SUMMARIZECOLUMNS, cannot be used directly inside certain calculated columns or measures. These functions are primarily used in table expressions or row contexts.
In your case, it seems like you are trying to create a measure called Dynamic_WIPv2 that summarizes data based on certain conditions. Instead of using SUMMARIZECOLUMNS directly in the measure, you may want to consider using it in a separate calculated table or in a variable outside the measure.
Here's an example of how you might modify your measure:
Dynamic_WIPv2 =
VAR _table =
SUMMARIZECOLUMNS(
Sheet1[Simpro jobid],
FILTER(
Sheet1,
Sheet1[invoiceid] = MAX(Sheet1[invoiceid])
),
"WiP", SUM(Sheet1[WorkInProgress])
)
RETURN
SUMX(_table, [WiP])
In this modified version, the SUMMARIZECOLUMNS function is used to create a table _table, and then the SUMX function is used to iterate over that table and calculate the sum of the WiP column.
Read more about this dax function in below dax:
https://analyticpulse.blogspot.com/2023/11/summarize-and-summarize-column.html
If this helped, Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
https://instagram.com/analytic_pulse
https://analyticpulse.blogspot.com/
Thank you, but isn't your version the same as mine that I am getting the error on?
Cheers
Carl
You're correct, and I appreciate your clarification. I made an oversight in my response. I apologize for that.
Upon closer inspection, it seems the issue might be related to the use of the MAX function within the SUMX function. Try using CALCULATE to evaluate the measure in a context modified by the maximum invoiceid. Here's the revised measure:
Dynamic_WIPv2 =
VAR _invoiceid = MAX(Sheet1[invoiceid])
RETURN
CALCULATE(
SUM(Sheet1[WorkInProgress]),
Sheet1[invoiceid] = _invoiceid
)This modification avoids the use of SUMMARIZECOLUMNS and directly uses CALCULATE with the filtering condition. Please replace your existing measure with this version and check if it resolves the calculation error.
