Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Maximum difference between two tables

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,

 

Need Help
  • az38's avatar
    az38
    6 years ago

    Anonymous 

    try smth like

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

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    try smth like

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