Forum Discussion

Pfoster's avatar
Pfoster
Icon for Resolver I rankResolver I
1 year ago
Solved

Can not find error in Measure

I have following Measure: Price Effect = SUMX ( 'Sales Data', IF ( 'Sales Data'[Company Code] IN {"PF3091", "PF3HGS", "PF3262", "PF3267"} || [NSPFocus_LC] = 0 ...
Need Help
Tips and Tricks
  • Elena_Kalina's avatar
    Elena_Kalina
    1 year ago

    Hi Pfoster 

    It looks like your measure is always returning 0 because of how the conditions in your IF statement are structured. Let's analyze the issue:

    Problem Analysis:

    1. Your IF statement returns 0 if ANY of the conditions are true (due to the OR operators ||)

    2. The calculation only happens when ALL conditions are false

    3. The most likely issue is that one of your conditions is always evaluating to true for all rows

    Try debugging by isolating each condition:

    Price Effect Debug = 
SUMX (
    'Sales Data',
    VAR Condition1 = 'Sales Data'[Company Code] IN {"PF3091", "PF3HGS", "PF3262", "PF3267"}
    VAR Condition2 = [NSPFocus_LC] = 0
    VAR Condition3 = [NSPComp_LC] <= 0
    VAR Condition4 = 'Sales Data'[Material] = "1000222"
    RETURN
        IF(
            Condition1 || Condition2 || Condition3 || Condition4,
            0,
            ([NSPFocus_LC] - [NSPComp_LC]) * [FX_Rate_CompPeriod] * [FocusMT] * 1000
        )
)

    Then create separate measures to check each condition:

    Condition1_Count = 
COUNTROWS(
    FILTER(
        'Sales Data',
        'Sales Data'[Company Code] IN {"PF3091", "PF3HGS", "PF3262", "PF3267"}
    )
)

Condition2_Count = 
COUNTROWS(
    FILTER(
        'Sales Data',
        [NSPFocus_LC] = 0
    )
)

Condition3_Count = 
COUNTROWS(
    FILTER(
        'Sales Data',
        [NSPComp_LC] <= 0
    )
)

Condition4_Count = 
COUNTROWS(
    FILTER(
        'Sales Data',
        'Sales Data'[Material] = "1000222"
    )
)
Elena_Kalina's avatar
Elena_Kalina
Icon for Solution Sage rankSolution Sage
1 year ago

Hi Pfoster 

It looks like your measure is always returning 0 because of how the conditions in your IF statement are structured. Let's analyze the issue:

Problem Analysis:

  1. Your IF statement returns 0 if ANY of the conditions are true (due to the OR operators ||)

  2. The calculation only happens when ALL conditions are false

  3. The most likely issue is that one of your conditions is always evaluating to true for all rows

Try debugging by isolating each condition:

Price Effect Debug = 
SUMX (
    'Sales Data',
    VAR Condition1 = 'Sales Data'[Company Code] IN {"PF3091", "PF3HGS", "PF3262", "PF3267"}
    VAR Condition2 = [NSPFocus_LC] = 0
    VAR Condition3 = [NSPComp_LC] <= 0
    VAR Condition4 = 'Sales Data'[Material] = "1000222"
    RETURN
        IF(
            Condition1 || Condition2 || Condition3 || Condition4,
            0,
            ([NSPFocus_LC] - [NSPComp_LC]) * [FX_Rate_CompPeriod] * [FocusMT] * 1000
        )
)

Then create separate measures to check each condition:

Condition1_Count = 
COUNTROWS(
    FILTER(
        'Sales Data',
        'Sales Data'[Company Code] IN {"PF3091", "PF3HGS", "PF3262", "PF3267"}
    )
)

Condition2_Count = 
COUNTROWS(
    FILTER(
        'Sales Data',
        [NSPFocus_LC] = 0
    )
)

Condition3_Count = 
COUNTROWS(
    FILTER(
        'Sales Data',
        [NSPComp_LC] <= 0
    )
)

Condition4_Count = 
COUNTROWS(
    FILTER(
        'Sales Data',
        'Sales Data'[Material] = "1000222"
    )
)
Pfoster's avatar
Pfoster
Icon for Resolver I rankResolver I
1 year ago

Thank you, that helped a lot. Now, I have find the two conditions, which causes trouble: The Company Code Part and also the Material Code brought up the problem. Now, I am able to fix it.