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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
btfergie
Helper I
Helper I

Grid smoothing DAX

I am trying to figure out a way to come up with a way to take a 2D XY grid of table values and come up with smoothed values by using the neighboring cells.  Perhaps this would use some sort of distance weighted algorithm.  I would use this to look for anomolies in the data and replace them with the estimated value if it exceeds a threshold.

 

Thanks.

11 REPLIES 11
lbendlin
Super User
Super User

That should be rather simple, but you won't be able to tune it nicely - you can use a parameter value to twist the knob but it is not nearly as interactive as a slicer. Let's try both ways and compare the outcome.

 

In Power BI you can use the Play Axis to show multiple generations...

lbendlin_1-1629768224137.png

 

You can calculate the second generation table thusly:

Table2 = SELECTCOLUMNS('Table',"X",'Table'[X],"Y",'Table'[Y],"Generation",2)

Value = 
var x = Table2[X]
var y = Table2[Y]
return CALCULATE(max('Table'[Value]),'Table'[X]=x,'Table'[Y]=y) -- plus the weighting of our choice of the neighboring cells)

That looks like it is going to work.  Thanks!

lbendlin
Super User
Super User

This feels a little too harsh. 

lbendlin_0-1629757349949.png

 

I think it should start at 1 in the middle and then use twice the distance

lbendlin_1-1629757701730.png

or even more agressive.

 

Perhaps I shouldn't have used the word smoothing but rather prediction based on surrounding cells.  I am sure there are more and less aggressive ways of doing it.  It would be nice to be able to change the aggressiveness with a filter.

The part I can't figure out is how do I use the surrounding cell values and distances to come up with an estimate of each cell.

That's the easy part ( and why I mentioned the Game Of Life).  You maintain two separate surfaces.  One for display and one for computation. Once you have computed a new generation you blip it onto the screen.

 

Of course in Power BI you can't do that, it has no memory.  You will need to show the new generation screen (table) next to the old one, and feed it with measures.

 

I'll play with your sample data to see what I can come up with

Yes, I get the idea and I could probably come up with something in Excel.  I am trying to figure it out in Power Query.

lbendlin
Super User
Super User

sounds like a fun exercise. Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.

Here is a sample table:

X Y Value
-5 5 0.82
-4 5 1.15
-3 5 1.44
-2 5 1.33
-1 5 1.46
0 5 1.51
1 5 1.48
2 5 1.67
3 5 1.62
4 5 0.82
5 5 0.59
-5 4 1.09
-4 4 1.28
-3 4 1.42
-2 4 1.4
-1 4 1.58
0 4 1.49
1 4 1.7
2 4 1.52
3 4 1.52
4 4 1.15
5 4 0.89
-5 3 1.3
-4 3 1.56
-3 3 1.46
-2 3 1.56
-1 3 1.4
0 3 1.36
1 3 1.48
2 3 1.33
3 3 1.51
4 3 1.1
5 3 0.92
-5 2 1.26
-4 2 1.72
-3 2 1.66
-2 2 1.4
-1 2 1.25
0 2 1.32
1 2 1.31
2 2 1.33
3 2 1.48
4 2 1.33
5 2 1.2
-5 1 0.91
-4 1 1.56
-3 1 1.72
-2 1 1.19
-1 1 1.02
0 1 1.07
1 1 1.21
2 1 1.19
3 1 1.48
4 1 1.79
5 1 1.52
-5 0 0.52
-4 0 0.24
-3 0 0.29
-2 0 0.37
-1 0 0.11
0 0 0
1 0 0.43
2 0 0.95
3 0 1.3
4 0 1.3
5 0 1.16

Below is a dashbboard image.  The tables were too big to add;  I'll have to cut them down somehow.  I was able to do a simple average of immediately adjacent cells but haven't figured out anything more complex.  I would like to be able to do some sort of Inverse Distance Weighting or other interpolation.

In my example, I am replacing values outside and expected range with an average of the 4 or less adjacent cells.

Thanks.

 

 

 

 

 

btfergie_0-1629743302757.png

 

Ever played the "Game of Life"?  Of the eight adjacent cells do you assume they all have the same weight, or is the weight of the corner cells less than the edge cells?

Yes.  The corners could have a weight of 1/1.41 vs 1 for edge.  I would ideally want to include more distant cells and could have the weight as the inverse of distance.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors