Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
h4tt3n
Resolver II
Resolver II

Looking for measure that aggregates values by day/week/month, then returns max aggregated value

What I got:
Loads of timestamped data, one row per hour. 

2020-07-09 15_58_45-Window.png

 

 

 

 

 

 

 

 

 

 

 

 

I then aggregate the data in days, weeks, and months. The result is correct, but the scale to the left has very big numbers.

screenie_columns.png

 

What I want:

I want a DAX measure that aggregates the data in either days, weeks, or months in a non -visible table, then returns the value of the largest column. In short, the max of a max.
screenie_whatIwant.png

 

Then I will use this value to find the correct factor with which to divide the sums, eg. a factor 1000 to get kWh from Wh.

Thanks in advance,

 

Cheers, Mike

 

 

3 REPLIES 3

I think something like this may work for you. 

 

First I created a measure for your Value

sumValue = SUM(Table1[Value])

 

Then used it in the calculation below.

The ISFILTERED serves to give you the granularity currently displayed.

maxValue2 = 
IF(ISFILTERED(Table1[TimeStamp].[Day]),
    CALCULATE(MAXX(VALUES(Table1[TimeStamp].[Day]), [sumValue]), ALL(Table1)),
    IF(ISFILTERED(Table1[TimeStamp].[Month]), 
        CALCULATE(MAXX(VALUES(Table1[TimeStamp].[Month]), [sumValue]), ALL(Table1)),
        IF(ISFILTERED(Table1[TimeStamp].[Quarter]),
            CALCULATE(MAXX(VALUES(Table1[TimeStamp].[Quarter]), [sumValue]), ALL(Table1))
        )     
    )
)

 This assumes you have a hierarchy on your TimeStamp. You can also replace Day with Date from that hierarchy.

 

Thanks for the answer, I am looking into it. How would I orbganize my timestamp hierarchically, so I could use this syntax? 

Table1[TimeStamp].[Day]
Table1[TimeStamp].[Week]
Table1[TimeStamp].[Month]

 With the dot and the extra "column"?

 

Cheers, Mike

With fields that are date or datetime, it should happen automatically. When you import the table, just be sure to set the data type. [Select your column from the fields pane, go to the Modeling tab, change "Data type" to Date/Time] Then when you drag that field onto a visual like a Matrix, you'll see this in the rows section:

 

CoalesceIsMore_0-1594390547749.png

 

But it won't look much different in the Table / Field list before you do this, so you just have to know Power BI will treat those date/time fields like this, and give you the option for that dot notation.

 

If you have trouble with the hierarchy, you could always just create a date dimension, or add columns to your table to extract the date, month, quarter, year. Then you could access them directly without the dots.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors