Finding the Row Related to the Maximum Result Found by an Expression in an Iterator Function

[russell80]

I have tables ('Meter Addition', 'Meter Subtraction')of time series data from multiple meters linked to a date table, 'DateTable', with columns DateTime, Day of Week, Hour, Minute and I've created a measure to combine the meter readings together for each timestamp.

Combined Meter Reading =
MAXX (
    VALUES ( 'DateTable'[DateTime] ),
    IF (
        ISFILTERED ( 'Meter Addition'[Meter Name] ) = TRUE,
        CALCULATE ( SUM ( 'Meter Addition'[Reading] ) ),
        BLANK ()
    )
        - IF (
            ISFILTERED ( 'Meter Subtraction'[Meter Name] ) = TRUE,
            CALCULATE ( SUM ( 'Meter Subtraction'[Reading] ) ),
            BLANK ()
        )
)

I've created a new measure that calulates the average of the readings as follows:

Combined Avg Meter Reading =
AVERAGEX (
    VALUES ( 'DateTable'[DateTime] ),
    [Combined Meter Reading]
)

I've added a table to my report with the column [Day of Week] from the 'DateTable' to show the largest average combined meter reading for that day and the [Hour] and [Minute] it occured from the 'DateTable'. I can get the largest average combined meter reading just by adding the [Combined Avg Meter Reading] measure to the table. I can also create a line chart with a legend for [Day of Week] and axis with [Hour] and [Minute] and [Combined Avg Meter Reading] for the values. Doing this I can see visually the time when the largest reading occurs, but I don't know how to show that on a table?

[v-henryk-mstf · Accepted solution]

Hi @russell80 ,

So you mean you want show these results into a data model table instead of power bi visuals? AFAIK, current measure expression are calculated based on row contexts and correspond filters.

If you use them into the calculated column/tables, their calculate range will be change and fixed and cannot directly change based on filter effects.

Regards,

Xiaoxin Shen

[russell80]

Slight correction to the last paragraph. I cannot get the largest average combined meter reading just by adding the [Combined Avg Meter Reading] measure to the table. When I do this I get the average for the full day.