Reply
russell80
Helper III
Helper III

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

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?

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

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
Helper III
Helper III

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.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)