MdxScript(Model) (20,1) calculation error

[CarlBlunck]

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:

Dynamic_WIPv2 =

VAR _table =

SUMMARIZECOLUMNS(

    Sheet1[Simpro jobid],

    FILTER(

        Sheet1,

        Sheet1[invoiceid] = MAX(Sheet1[invoiceid])

    ),

    "WiP",

    SUM(Sheet1[WorkInProgress])

         )

RETURN

SUMX(_table, MAX([WiP]))

Can anyone see the issue?

This is to solve this - Re: Sum of values where max id is less than dynami... - Microsoft Fabric Community 

[AnalyticPulse]

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/

[CarlBlunck]

Hi @AnalyticPulse 

Thank you, but isn't your version the same as mine that I am getting the error on?

Cheers

Carl

[123abc]

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.

[123abc]

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.

[CarlBlunck]

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

[123abc]

Let's modify the measure to achieve this. Instead of calculating the maximum invoiceid for the entire table, we'll calculate it for each Simpro jobid. Here's an updated measure:

Dynamic_WIPv2 =
CALCULATE(
SUM(Sheet1[WorkInProgress]),
ALLEXCEPT(Sheet1, Sheet1[Simpro jobid]),
Sheet1[invoiceid] = MAX(Sheet1[invoiceid])
)

This modification uses ALLEXCEPT to remove all filters from the Sheet1 table except for the Simpro jobid. It then applies the filter condition for the maximum invoiceid within that context.

Please replace your existing measure with this version and see if it gives you the expected result.

[Anonymous]

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 _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.