Forum Discussion
MdxScript(Model) (20,1) calculation error
I am getting this : MdxScript(Model) (20,1) calculation error Calculation error in measure 'Sheet1'[Dynamic_WIPv2]: SummarizeColumns() and AddMissingItems() may not be used in this context.
Here is the measure:
7 Replies
The error message suggests that the issue is related to the use of the SUMMARIZECOLUMNS function along with SUMX in your DAX measure. In certain contexts, the combination of SUMMARIZECOLUMNS and SUMX might not be allowed.
To address this issue, you can simplify your measure and avoid using SUMMARIZECOLUMNS in this specific case. Here's an alternative approach using a combination of FILTER and RELATEDTABLE:
Dynamic_WIPv2 =
VAR _table =
FILTER(
ALL(Sheet1),
Sheet1[invoiceid] = MAX(Sheet1[invoiceid])
)
RETURN
SUMX(_table, Sheet1[WorkInProgress])In this revised measure, I replaced SUMMARIZECOLUMNS with FILTER and removed the unnecessary aggregation of the WiP column in the _table variable, as you are already using SUMX to iterate over the table.
This modification should help eliminate the error you're encountering. Please give it a try and see if it resolves the issue. If you still encounter problems or have additional requirements, feel free to provide more details.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Hi 123abc
Thanks for the response and updated approach! That get's me close. Here is a link to the pbix file - https://drive.google.com/file/d/1n4Jbbml3_nw4UmdByPYTeOD2bhPiJ2B1/view?usp=drive_link
When I calculate this in excel manually, I get a result of $45,580.85. But the measure is computing a result of $31,200.
I need to be able to have the filter of Sheet1[invoiceid] = MAX(Sheet1[invoiceid]) applied for each distinct Sheet1[Simpro jobid]. That is really key.
Cheers
Carl
Hi CarlBlunck ,
If it's just the total value of the measure that's wrong, try adding another measure based on this one created by AnalyticPulse .
Dynamic_Wipv3 = VAR _a = table[Dynamic_WIPv2] VAR _b = SUMMARIZE ( table, table[date], "aaa", table[Dynamic_WIPv2] ) RETURN IF ( HASONEVALUE ( table[date] ), _a, SUMX ( _b, [aaa] ) ) //table[date] can be changed to a column with a unique value in the table.//How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- The error you're encountering indicates that the SUMMARIZECOLUMNS function, along with the FILTER function, is not allowed in the context where you're trying to use it. The error specifically mentions that "SummarizeColumns() and AddMissingItems() may not be used in this context."
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.
