Help with DAX formula

[Kolumam]

Hi All,

I am stuck with understanding the below formula,

Can anyone help me split up and clearly understand the formula?

Tariff (local currency) = IF('Meter Readings'[Date]<RELATED(Opportunities[COD__C]),CALCULATE(SUM(Tariff[Tariff]),FILTER(ALL(Tariff),AND(Tariff[Tariff Reference]=Related(Meter[Tariff Reference]),Tariff[Start of Tariff]-1<RELATED(Opportunities[COD__C])))),IF('Meter Readings'[Date]>CALCULATE(MAX(Tariff[Start of Tariff]),FILTER(ALL(Tariff),Tariff[Tariff Reference]=Related(Meter[Tariff Reference]))),CALCULATE(SUM(Tariff[Tariff]),FILTER(ALL(Tariff),AND(Tariff[Tariff Reference]=Related(Meter[Tariff Reference]),Tariff[End of Tariff]=CALCULATE(MAX(Tariff[End of Tariff]),FILTER(All(Tariff),Tariff[Tariff Reference]=Related(Meter[Tariff Reference])))))),CALCULATE(SUM(Tariff[Tariff]),FILTER(ALL(Tariff),AND(Tariff[Tariff Reference]=Related(Meter[Tariff Reference]),AND(Tariff[Start of Tariff]<='Meter Readings'[Date],Tariff[End of Tariff]+1>'Meter Readings'[Date]))))))

[Anonymous]

Assuming you are familer with CALCULATE function,I hope the below split up will help you.

Tariff (local currency) = 

IF('Meter Readings'[Date]< RELATED(Opportunities[COD__C]),
 // If the above condition1 is true, execute the below part. Else, skip the below code and go to next part.

     CALCULATE(SUM(Tariff[Tariff]),
		FILTER(ALL(Tariff),
		AND(Tariff[Tariff Reference]=Related(Meter[Tariff Reference]),
		Tariff[Start of Tariff]-1<RELATED(Opportunities[COD__C])))),
//If condition1 is false, the below part will be evaluated, Lets say condition 2 
  IF('Meter Readings'[Date]>CALCULATE(MAX(Tariff[Start of Tariff]),FILTER(ALL(Tariff),Tariff[Tariff Reference]=Related(Meter[Tariff Reference]))),

// If condition2 is true,the below part will be evaluated,
	CALCULATE(SUM(Tariff[Tariff]),FILTER(ALL(Tariff),AND(Tariff[Tariff Reference]=Related(Meter[Tariff Reference]),Tariff[End of Tariff]=CALCULATE(MAX(Tariff[End of Tariff]),FILTER(All(Tariff),Tariff[Tariff Reference]=Related(Meter[Tariff Reference])))))),

//If condition2 is false, the below part will be evaluated,

CALCULATE(SUM(Tariff[Tariff]),FILTER(ALL(Tariff),AND(Tariff[Tariff Reference]=Related(Meter[Tariff Reference]),AND(Tariff[Start of Tariff]<='Meter Readings'[Date],Tariff[End of Tariff]+1>'Meter Readings'[Date]))))))

[Kolumam]

Hi Rajendran,

Thanks a lot for your help. Can you please help me with the definition calculate function and the filter function in the IF statement?

[Anonymous · Accepted solution]

CALCULATE function evaluate the expression ( whish is first argument) , after appling the conditions mentioned in argument 2.Bassically the2nd argumumet changes the context of the first argument.

For ex: Lets calculate the total sales amt. The first example calculates the overall sales whereas the second example calculates the sales for USA only.

SUM(SALES.sales_amt)

CALCULATE( SUM(SALES.sales_amt), FILTER( SALES, country= "USA")

Hope this clarifies you.

Thanks

Raj