Forum Discussion

Pfoster's avatar
Pfoster
Resolver 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
            || [NSPComp_LC] <= 0
            || 'Sales Data'[Material] = "1000222",
        0,
        ([NSPFocus_LC] - [NSPComp_LC]) * [FX_Rate_CompPeriod] * [FocusMT] * 1000
    )
)

but unfortunately, it is showing always 0 as result, even I am expecting another result. I do not get, where the error is. 
Somehow, the filtering is not working correctly, the calculation is correct, without the If conditions, the measure is showing the expected result, but unfortunately also for the conditions, I want to exclude. Where is the mistake? 

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"
    )
)

3 Replies

  • Elena_Kalina's avatar
    Elena_Kalina
    Solution 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
      Resolver 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. 

  • Deku's avatar
    Deku
    Super User
    1 year ago

    Have you tried adding each conditions of the if statement into a table, with all the columns of the sales table and manually checking cases where the condition would be true to validate?

     

    Without seeing the data or definition of the other measures it is not possible to comment further.