Urgent Help Multiple if statements apply multiple functions in 1 DAX measure.

[GKK1]

Hi Power BI Community,

I am trying to use multiple if statements and IF CONDITION of 1st if statment met then I want to subtract my main Hours column from another table's hour column. And if the 2nd if statment condition is also met then I want to use the calculated value in first if statement and then add hours column value to it. I am using the below statment, which is giving me values from just first if statement. 
I think I need to use variables to complete this task , however, not sure how to. 
I need to use this measure in a Matrix. 

I will really appreciate urgent help on this.

Best regards,

GK

measure1 = SUMX(

    ADDCOLUMNS (

        VALUES ( DimDate[Day] ),

        "@condition",

if(

    CALCULATE(SELECTEDVALUE(TableA[SITEID]) = SELECTEDVALUE('TableB[SITEID])),

    CALCULATE(SUM('HOURS'[PAY_HOURS]) - SUM('TableB'[Daily Hours])),

    if(

        CALCULATE(SELECTEDVALUE(TableA[SITEID]) = SELECTEDVALUE('TableC[SITEID])),

        CALCULATE(SUM('HOURS'[PAY_HOURS]) + SUM('TableC'[Daily Hours])),        

        SUM('HOURS'[PAY_HOURS])

        )

)

),

    [@condition] )

[sevenhills]

Curious to know, what results are coming for this measure? Will this work? Without knowing the data model tough but guessing based on your code ... 

measure1 = 
var _a_siteid = SELECTEDVALUE(TableA[SITEID])
var _b_siteid = SELECTEDVALUE(TableB[SITEID]
var _c_siteid = SELECTEDVALUE(TableC[SITEID])

RETURN SUMX( 
        VALUES ( DimDate[Day] ),
           SWITCH ( TRUE ()
            , _a_siteid = _b_siteid, SUM('HOURS'[PAY_HOURS]) - SUM('TableB'[Daily Hours])
            , _a_siteid = _c_siteid, SUM('HOURS'[PAY_HOURS]) + SUM('TableC'[Daily Hours])
            , SUM('HOURS'[PAY_HOURS])
           )
    ) 

[sevenhills]

I used your above data sample and add this measure to TableA

Measure1 =  SUMX( 
            VALUES ( TableA[SiteID] ),
            CALCULATE( SUM('TableA'[HOURS]) - SUM('TableB'[Subtract Hours]) + SUM('TableC'[Add Hours]) )
           )
    

Output: 

Your result image posted by you, has some calculation error as you are expecting to subtract and then add for the corresponding values. That is the reason first 4 rows matched and the last 2 did not match. Hope this helps!

[GKK1]

Thanks for your response. And No, that is not the error in the last two rows. In some cases the SITEID might be missing from Table B and Table C. So if you see in Table B we do not have SITEID E and in Table C we don't have SITEID F. So the results seems fine to me as per your calculations. Thanks.
I need to use this measure in a matrix where I need the total of this on daily basis, so need to figure out the total value for the measure. Individual values are working fine. 

Need to add this part of the code:

measure1 = SUMX(

    ADDCOLUMNS (

        VALUES ( DimDate[Day] ),

        "@condition", ...............

[sevenhills]

First check point: Is the measure code working for individual values?

Yes, then post the code what you are looking for matrix, as my feeling is the code given works for it also or we can adjust to your needs. Thanks

[GKK1]

Yes, your code is working for individual values. I am sharing the screenshot of the matrix here. I am using the SITEID in the filter so the results here are for one site only. 
Now if I check the Total at the end that is working for a single value addition and then single for subtraction. Which is subtracting 6 and then adding 11 from the totals. It is not giving me the total of week which should be subtract 42 and then add 77 from the totals.
In the last row I have used the

subt_Daily = SUMX(

            VALUES ( DimDate[Day] ),

            CALCULATE( SUM('Locations to add Values'[Daily Hours]) )

           )
to get the total of the week.

[sevenhills]

Could you share the data, model or .pbix by removing the sensitive info? 

I see with my measure code, it is working fine

I am suspecting, since you are using day as column, would like to check the measure syntax? 

Thanks

[GKK1]

Yes, you are right. I am using Day as a Column. 

This is a Matrix Visual and I have turned on the from Values "Switch values to row groups rather than columns" to get the format and on Daily basis.

So the measure to get correct totals I have used this:
subt_daily = SUMX(

            VALUES ( DimAllTimeView_AccountingDate[Day] ),

            CALCULATE( SUM('Locations to add Values'[Daily Hours]) )

           )

[sevenhills]

It is not clear. In my screenshot above, I had the matrix and enabled the switch values to rows... and it works.

Let us try: 

~ Post the "Measure 1" syntax here

~ Similar to what you have done for subt_daily measure above, create (as temporarily) as 

subt_daily1 = SUMX(

            VALUES ( DimAllTimeView_AccountingDate[Day] ),

            CALCULATE( SUM('Locations to subtract Values'[Daily Hours]) )

           )

  and see if the value comes as 42 (= 6 * 7)

subt_daily2 = 

SUMX(

            VALUES ( DimAllTimeView_AccountingDate[Day] ),

            CALCULATE( SUM('Locations'[Hours]) ) -- use row1 Hours column for this

           )

~ Overall, the values has to show for you as 77, 42, 1490 for measures subt_daily, subt_daily1, subt_daily2

If this works then you can adjust the formula as 1490 + 77 - 42

[GKK1]

It is returning kind of similar results with very high values.

Sample data

[sevenhills]

Try this, assuming my other reply is working for you ... 

Measure1 =  SUMX( 
            VALUES ( DimInventSite[SITEID] ),
            CALCULATE( SUM(DimInventSite[Daily Hours]) - SUM('Locations to Subtract Values'[Daily Hours]) + SUM('Locations to add Values'[Daily Hours]) )
           )

[gmsamborn]

Hi @GKK1 

Does this help?

measure1 = 
	SUMX(
		ADDCOLUMNS (
			VALUES ( DimDate[Day] ),
			"@condition",
				IF(
					SELECTEDVALUE( TableA[SITEID] ) = SELECTEDVALUE( 'TableB[SITEID] ),
					SUM( 'HOURS'[PAY_HOURS] ) - SUM( 'TableB'[Daily Hours] ),
					IF(
						SELECTEDVALUE( TableA[SITEID] ) = SELECTEDVALUE( 'TableC[SITEID] ),
						SUM( 'HOURS'[PAY_HOURS] ) + SUM( 'TableC'[Daily Hours] ),
						SUM( 'HOURS'[PAY_HOURS] )
					)
				)
		),
	[@condition]
	)

Without data, it'd be pretty tough to be sure.  I'm just going by your code.

[GKK1]

Thanks for trying, but this did not work. 😞

[gmsamborn]

What was wrong?

Would this help?

measure1 = 
	SUMX(
		ADDCOLUMNS (
			VALUES ( DimDate[Day] ),
			"@condition",
				IF(
					SELECTEDVALUE( TableA[SITEID] ) = SELECTEDVALUE( 'TableB[SITEID] ),
					CALCULATE(
						SUM( 'HOURS'[PAY_HOURS] ) - SUM( 'TableB'[Daily Hours] )
					),
					IF(
						SELECTEDVALUE( TableA[SITEID] ) = SELECTEDVALUE( 'TableC[SITEID] ),
						CALCULATE(
							SUM( 'HOURS'[PAY_HOURS] ) + SUM( 'TableC'[Daily Hours] )
						),
						SUM( 'HOURS'[PAY_HOURS] )
					)
				)
		),
	[@condition]
	)

If not, you can share an example file by uploading it to OneDrive, GoogleDrive, Dropbox, etc and share the link.

[GKK1]

This one has the same issue, it is just applying the first IF Statement and subtracting the hours. But not adding the hours in the result of previous IF statement. Sample data with results.