Maximum difference between two tables

[Anonymous]

Hi all,

I have two tables, ones containing measured values by some sensorsand one containing estimated values for those locations.  Both tables contain results for a 24hours duration. Each table have the following relevant columns:

- Sensor ID

- Hour

- Value measured/calculated

- Auxiliar ID which concatenate the sensor ID with the hour , which allows to stablish a relationship between the two tables to compare results for each timestep

For each sensor, I wanted a table that showed the maximum difference between calculated and measured values so I can see in which sensors my predicted values are off. I used the following DAX:

MDiff = maxx(RELATEDTABLE(Calculated Values),(MAXX(Merge1,abs(Merge1[Measured value] - ModelResults[Calculated Value]))))

The problem I have is that it seems to ignore the Sensor context (see images below) and it seems to compare against every sensor. I know potentially I could create a joined table using PowerQuery but I was wondering whether I could implement the calculation using DAX.

Many thanks,

[mahoneypat]

While it would be better to append your table (with predicted/measured with same column name), and pivot that column out to get predicted and measured on same rows, please try this approach as a Measure instead of a calculated column.

Max Difference = MAXX(MeasuredTable, MeasuredTable[MeasuredValue] - RELATED(PredictedTable[PredictedValue]))

This assumes there is a 1:1 relationship (or Many:1) between the Measured and Predicted tables.  Use the measure in a table visual with your SensorID column from the MeasuredTable.  You could also use the measure in a line chart to visualize the different at each hour.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks Mahoneypat, but it seems the related function does not accept any column from my related table

[az38 · Accepted solution]

@Anonymous 

try smth like

CALCULATE(
MAXX(Merge1,abs(Merge1[Measured value] - ModelResults[Calculated Value])),
ALLEXCEPT(Merge1, Merge1[Sensor ID])
)