SUMX not working in Matrix Table

[cj_oat]

Hi,

I have an issue when using SUMX to sum Measure below, however, when I put it in Matrix Table, it shows "Query has exceeded the available resources.", is there any other formula that can sum Measure (or to make below Measure to sum itself when showing Subtotal or Grand Total)

Active Count = SWITCH(TRUE(),[__Active]=1 && ([__Sales]>0 || [Inventory]>0),1,BLANK())

[Kedar_Pande]

@cj_oat 

Updated Measure

Active Count =
SUMX(
ADDCOLUMNS(
VALUES('YourTableName'[KeyColumn]), 
"__ActiveResult", SWITCH(
TRUE(),
[__Active] = 1 && ([__Sales] > 0 || [Inventory] > 0), 1, BLANK()
)
),
[__ActiveResult]
)

💌 If this helped, a Kudos 👍 or Solution mark ✔️would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[123abc]

You could create a calculated table or a new measure that aggregates the data first and then applies the SUMX:

Active Count Aggregated = SUMX( VALUES('YourTable'[Category]), SWITCH(TRUE(), [__Active] = 1 && ([__Sales] > 0 || [Inventory] > 0), 1, BLANK() ) )

This way, you're only working with unique categories or groupings, which reduces the load.

• Optimize the SWITCH Function: If the SWITCH function is causing heavy computation, you might want to simplify it or avoid unnecessary checks. For instance, check if it can be reduced to just one condition or if the data model could be simplified by preprocessing some calculations outside of DAX.

• Use SUM instead of SUMX in Totals: In some cases, using SUM instead of SUMX can work better in terms of performance, particularly for totals or subtotals. However, this depends on your specific calculation.

  You can try using SUM over a simplified version of your measure:

  Active Count Total = SUM('YourTable'[Active Count Column])

  This approach calculates the Active Count for each row and then sums it up for subtotals and grand totals.