Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]))))))
Solved! Go to 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
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?
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |