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.
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.
Solved! Go to Solution.
Hi @Jasel,
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])
Hi @Jasel,
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])
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |