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. ...
Need Help
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

      • harrinho's avatar
        harrinho
        Helper III
        7 years ago

        Thanks Interkoubess. It's because these hours are based on and driven by different projects. Another example below, hopefully shows more clear. 

         

        Your calculation works fine but the problem is that because I summarize this to one line, when it comes to the total row, the calculation is not applied. For example here, I jsut want to roll it up to one line, getting rid of the Projects Column

         

        Then I will have the following 

        But in this case I'd like the Total Allocations to return 43+38=81 instead