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.
Hello,
I've got a line chart with incremental hour in my axis, in my table there could be zero, one or more values for each hour.
The basic function I'm starting from is calculate(average([value]), [item]="ItemINeed"), but I need my graph to substitute blank hours with the last single available value.
For example I've got value 40 and 50 at 9AM, no values at 10AM, value 60 at 11AM: my graph should prompt 45 at 9AM, 50 at 10AM and 60 at 11AM.
Is it possible in a dax measure? I think the problem is getting outside the time context of the line chart axis that acts like a filter on my measure also if I use variables.
Thank you in advance!
Solved! Go to Solution.
Hello
I think it is possible with a measure like this:
RT =
var _CurrentPeriod = SELECTEDVALUE('Table'[Period])
// select all periods upto and including the current period
var _Filtered =
Filter(
ALL('Table'),
'Table'[Period]<= _CurrentPeriod
)
// calculate averages per period and filter out all 0 values
var result =
TOPN(1,
Filter(
SUMMARIZE(
_Filtered,
'Table'[Period],
"AVG",[Measure]
),
'Table'[Period]<= _CurrentPeriod && [AVG]<>0
),
'Table'[Period],
desc
)
// have to do a sumx here because the result is a table (with one row)
return
sumx(result,[AVG])
So the only thing you have to make sure that you have a period value (your 9, 10 ,11 AM etc.)
Hope this helps,
Jan
if this is a solution for you, don't forget to mark it as such. thanks
Hello
I think it is possible with a measure like this:
RT =
var _CurrentPeriod = SELECTEDVALUE('Table'[Period])
// select all periods upto and including the current period
var _Filtered =
Filter(
ALL('Table'),
'Table'[Period]<= _CurrentPeriod
)
// calculate averages per period and filter out all 0 values
var result =
TOPN(1,
Filter(
SUMMARIZE(
_Filtered,
'Table'[Period],
"AVG",[Measure]
),
'Table'[Period]<= _CurrentPeriod && [AVG]<>0
),
'Table'[Period],
desc
)
// have to do a sumx here because the result is a table (with one row)
return
sumx(result,[AVG])
So the only thing you have to make sure that you have a period value (your 9, 10 ,11 AM etc.)
Hope this helps,
Jan
if this is a solution for you, don't forget to mark it as such. thanks
Not exactly what I needed but, your tips to use ALL() and TOPN() function in a SUMX() allowed me to solve it.
Thanks a lot!
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 | |
18 | |
16 | |
15 |
User | Count |
---|---|
51 | |
26 | |
22 | |
17 | |
16 |