Forum Discussion

harrinho's avatar
harrinho
Helper III
7 years ago

IF Statement

Hi, 

 

I have the following table with measures from two different sources, however, joins on ID are fine and working.

 

My issue is that the if statement i have built doesn't return any result. My IF creteria as shown in the screenshot and the code below are:

 

If Soft Hours = Hard hours, return Hard hours

If Soft Hours = 0, return Hard hours

IF Soft Hours > Hard Hours, return Soft+Hard

If Soft Hours < Hard hours, return Hard hours. 

 

 

 

 

Both sources contain multiple rows for teh same pmfkey and the hours are rolled up to this key. 

Total Allocations = 
IF(CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))=CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),
    IF(CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))=0,CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),
        IF(CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))>CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours])+SUM('CLARITY HARD ALLOC'[Hard_Hrs])),
            IF(CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))<CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs]))))))

 

Literally I don't understand why Power BI cannot handle a dead simple sequence of criteria and return random results. The only case it works is when Soft = 0 and returns the Hard hours for ACHSA09. I chose "don't summarize" from the filters pane so as you can see the full rows deployement. 

 

Is there any other way to achieve this ?

 

Thanks

Need Help

5 Replies

  • Interkoubess's avatar
    Interkoubess
    Solution Sage
    7 years ago

    Hi harrinho,

     

    I have a question to better understand your request.

    Why 4 conditions because I only see 1 if ( If I am correct):

    1. If Soft Hours > Hard Hours, return Soft+Hard else Hard hours

     

    Please check this and let us know.

     

    Ninter

     

     

    • harrinho's avatar
      harrinho
      Helper III
      7 years ago

      Indeed, I just deployed it in detail to explain the context better :) 

       

      I amended the query as per below 

       

      Total Allocations = IF(CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))>CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours])+SUM('CLARITY HARD ALLOC'[Hard_Hrs])),CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])))

      But I get odd results again 

       

      • Interkoubess's avatar
        Interkoubess
        Solution Sage
        7 years ago

        Hi harrinho,

         

        Ok fine but I want to understand now your table why do you have for example for PMF Key (ACHS09) 3 times 0 for Soft hours and 3 times 32 for hard hours?

         

        So to sum up for this it will be 0 for soft hours and 96 for hard hours,  correct ?

         

        I will change my formula to something like this while waiting for your inputs...

         

        Total Allocations = var SH= CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))
var HH= CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])) return IF(SH> HH, SH+HH, HH)

        Ninter