Forum Discussion

CarlBlunck's avatar
CarlBlunck
Resolver I
2 years ago

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.     Her...
Need Help
123abc's avatar
123abc
Community Champion
2 years ago

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's avatar
    CarlBlunck
    Resolver I
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

       

       

       

       

    • 123abc's avatar
      123abc
      Community Champion
      2 years ago

      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.