DAX - Measure to calculate repeatability of values in three separate months (multiple cases)

[Anonymous]

Hi, I have to rebuilt a tool that was previously built using Excel VBA in Power BI. I need to analyze test data from different months to come up with a repeatability ECF value. With VBA it was done with loops and cases. The data used for the calc will be from the same MeterID and in different months. Some of the MeterID's will have multiple ECF's in one month so I'll need to loop through and calculate multiple times giving the lowest absolute value possible to populate in the visualization field.

Formula used to calculate ECF results:

% Difference ECF = Mean ECF (3 test points) – Smallest ECF (last test) / Mean ECF (3 test points)

effluent_test_summary

MeterID		TestDate		ECF
Meter1		2022-02-21		0.965
Meter1		2022-02-15		0.938
Meter1		2022-01-25		0.939
Meter1		2022-01-15		1.047
Meter1		2022-12-15		0.912
Meter2		2022-02-21		0.969
Meter2		2022-02-15		1.036
Meter2		2022-01-15		0.937
Meter2		2022-12-14		0.987
Meter3		2022-02-21		1.038
Meter3		2022-02-15		1.046
Meter3		2022-01-13		1.004
Meter3		2022-12-15		1.047
Meter3		2022-12-01		0.961

Ex. using formula and table from above:

Meter1, scenerio 1 calc:

- 2022-02-21, 2022-01-25, 2022-12-15 tests

- (AVG(0.965,0.939,0.912)-0.912)/AVG(0.965,0.939,0.912) = 0.0284

Meter1, scenerio 2 calc:

- 2022-02-21, 2022-01-15, 2022-12-15 tests

- (AVG(0.965,1.047,0.912)-0.912)/AVG(0.965,1.047,0.912) = 0.0643

Meter1, scenerio 3 calc:

- 2022-02-15, 2022-01-25, 2022-12-15 tests

- (AVG(0.938,0.939,0.912)-0.912)/AVG(0.938,0.939,0.912) = 0.0190

Meter1, scenerio 4 calc:

- 2022-02-15, 2022-01-15, 2022-12-15 tests

- (AVG(0.938,1.047,0.912)-0.912)/AVG(0.938,1.047,0.912) = 0.0556

Scenerio 3 calc for Meter1 would give best result and be populated in the ECF Repeatability column of the visual field.

[Greg_Deckler]

@Anonymous So while you can do for loops in DAX and emulate while loops, most often looping is not necessary, for example I think you can use something like this:

Measure =
  VAR __Meter = MAX('effluent_test_summary'[MeterID])
  VAR __Average = AVERAGEX(FILTER('effluent_test_summary',[MeterID] = __Meter),[ECF])
  VAR __Min = MINX(FILTER('effluent_test_summary',[MeterID] = __Meter),[ECF])
RETURN
  DIVIDE(__Average - __Min, __Average)

[Anonymous]

Thanks @Greg_Deckler , I will look at using this method to help solve. The only issue will be utilizing only three rows of data for the average from different months. The AVERAGEX appears to average any available data for that meter. In the example, Meter1 has two tests in Feb, and two tests in Jan. The average calc has to be done with ECF's in different months (Feb, Jan & Dec). Because there are multiple in each month, I will need to evaluate multiple times and come up with which set gives the lowest repeatability result.