Value missing from Matrix table after using SUMX

[cj_oat]

Hi guys,

I'm trying to create Measure to count other Measure based on conditions below,

Qualified Flag Count =

VAR _ActiveFlag = if ([ActiveFlag]>0 , 1 , BLANK())

VAR _SalesInv = if ([Cumulative Sales] + [Inventory]>0 , 1 , BLANK())

VAR _QualifiedFlag = if(_ActiveFlag + _SalesInv =2 , 1 , BLANK())

VAR _Result = SUMX('Table' , _QualifiedFlag)

RETURN

_Result

however, in some months (07 Jun & 08 Jul for example) that I've highlighted in yellow in below screenshot, you may see that there is no value "1" populated even though it met criteria that I set here.

if I changed to below formula, I'll see "1" populated in 07 Jun & 08 Jul, but once I put SUMX, then that "1" disappeared from 07 Jun & 08 Jul (reason I put SUMX because I want to see SUM of Qualified Flag in this Matrix table as well)

Qualified Flag Count =

VAR _ActiveFlag = if ([ActiveFlag]>0 , 1 , BLANK())

VAR _SalesInv = if ([Cumulative Sales] + [Inventory]>0 , 1 , BLANK())

VAR _QualifiedFlag = if(_ActiveFlag + _SalesInv =2 , 1 , BLANK())

RETURN

_QualifiedFlag

[Anonymous]

Hi ,

Based on the description, try to create a table that includes the dates you need. Try using the following DAX formula.

Qualified Flag Count =
VAR _ActiveFlag = IF([ActiveFlag] > 0, 1, 0)
VAR _SalesInv = IF([Cumulative Sales] + [Inventory] > 0, 1, 0)
VAR _QualifiedFlag = IF(_ActiveFlag + _SalesInv = 2, 1, 0)
RETURN
SUMX(
    CALENDAR(MIN('Table'[Date]), MAX('Table'[Date])),
    _QualifiedFlag
)

Best Regards,

Wisdom Wu

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

[cj_oat]

Hi @Anonymous 

it still shows blank, I guess it's because I do not have data rows in source data table for those in June and July month...

[cj_oat]

Hi @FarhanJeelani ,

Thanks for the advices! I just checked and realized that in June & July, the rows of data does not exist in the source table. Is there any way to fix this?

I used to create table to combine all combination of SKU and month and other data but then it collapsed because the data is too big...

[Dreday24]

 Make since to me also 

[FarhanJeelani]

Hi @cj_oat ,

The issue you’re facing is due to how SUMX works within the row context. The problem arises because _QualifiedFlag is being evaluated in the wrong context when you use SUMX. To resolve this and get the correct results for both individual cells and the totals, you need to make sure the measure respects the context correctly.

Here’s the corrected measure:

Qualified Flag Count = 
SUMX(
    'Table',
    VAR _ActiveFlag = IF([ActiveFlag] > 0, 1, BLANK())
    VAR _SalesInv = IF([Cumulative Sales] + [Inventory] > 0, 1, BLANK())
    VAR _QualifiedFlag = IF(_ActiveFlag + _SalesInv = 2, 1, BLANK())
    RETURN _QualifiedFlag
)

Key Adjustments:

1. Moving Calculations Inside SUMX:

   • _ActiveFlag, _SalesInv, and _QualifiedFlag are now evaluated per row in the table context within SUMX.

2. Ensuring Proper Context:

   • By defining the logic inside the SUMX, the calculation works for each row of the table correctly, and the results can be aggregated as expected.

3. Check Table Rows:

   • Ensure that 'Table' contains all relevant rows for the months (e.g., ensure rows for June and July exist in the table, even if some fields are blank).

Debugging Tips:

• Use a calculated column to debug _ActiveFlag and _SalesInv if needed to ensure these intermediate values are being computed correctly for the months in question.
• Test _QualifiedFlag independently to confirm it produces the expected results for each row.

Why Your Initial Measure Failed:

• In the first version of your measure, _QualifiedFlag was returning BLANK() for certain months because the SUMX function was iterating over the table but _QualifiedFlag was evaluated outside of the row context.

Let me know if this works!

Please mark this as solution if it helps. Appreciate Kudos.