cancel
Showing results for
Did you mean:
Highlighted
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
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
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 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,

```
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?

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

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 255 members 3,030 guests
Recent signins: