cancel
Showing results for
Search instead for
Did you mean:
Kolumam Member

## Help with DAX formula

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]))))))`
1 ACCEPTED SOLUTION

Accepted Solutions
rajendran Super Contributor

## Re: Help with DAX formula

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

3 REPLIES 3
rajendran Super Contributor

## Re: Help with DAX formula

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 Member

## Re: Help with DAX formula

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?

rajendran Super Contributor

## Re: Help with DAX formula

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

## Helpful resources

Announcements Top Ideas Top Kudoed Authors
Users Online
Currently online: 271 members 2,895 guests
Recent signins:
• elvismahadeo • njones2 • palvarez8 • Silvio_Carlos • kam545 • reric4 • Jugaso Please welcome our newest community members:
• nich1313 • jhovey • lindy1234 • KannanR • staceyrain • jegarcia • Renae9502 