Forum Discussion
Average from value above and below
Howdy all!
I have a set of data that is not fully complete. Here is an example:
| Date | TMAX | TMIN |
| 19380530 | 73 | 60 |
| 19380531 | 80 | 53 |
| 19380601 | 81 | -9999 |
| 19380602 | 75 | 63 |
The -9999 shows the incomple data.
Is there a way to check for values above or below in the calumn to figure out an avereage if the above and below values are not -9999? This is my main ask.
Secondary ask is the only other thing I figure I could do is replace the -9999 with blank or null, but not sure how to do that either since the replace function requires a number value to replace with. I'm sure I can create anohter column where if the value is -9999 than blank in DAX but that seems messy to me since I have quite a few columns with this issue.
Hi Anonymous,
This would be easier if you have an index column to help determin the rows before/after.
The Query Editor can possibly help with this, allthough you'll have to check your sorting.
Then if you create a calculated table like this you can build simple AVERAGE measures over it
Table = SELECTCOLUMNS( FILTER( CROSSJOIN ('Table1',SELECTCOLUMNS('Table1',"ID2",[Index],"TMAX2",[TMAX],"TMIN2",[TMIN])), [Index]>=[ID2]-1 && [Index] <= [ID2]+1 && [TMIN2] <> -9999 ),"Date" , [Date] , "TMIN" , [TMIN2] , "TMAX" , [TMAX2])
1 Reply
- Phil_Seamark
Microsoft Employee
Hi Anonymous,
This would be easier if you have an index column to help determin the rows before/after.
The Query Editor can possibly help with this, allthough you'll have to check your sorting.
Then if you create a calculated table like this you can build simple AVERAGE measures over it
Table = SELECTCOLUMNS( FILTER( CROSSJOIN ('Table1',SELECTCOLUMNS('Table1',"ID2",[Index],"TMAX2",[TMAX],"TMIN2",[TMIN])), [Index]>=[ID2]-1 && [Index] <= [ID2]+1 && [TMIN2] <> -9999 ),"Date" , [Date] , "TMIN" , [TMIN2] , "TMAX" , [TMAX2])