Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kolumam
Post Prodigy
Post Prodigy

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
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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]))))))


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
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.