Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
What I got:
Loads of timestamped data, one row per hour.
I then aggregate the data in days, weeks, and months. The result is correct, but the scale to the left has very big numbers.
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.
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
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |