cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kolumam Member
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
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
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
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
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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 255 members 3,030 guests
Please welcome our newest community members: