Fun with Graphing in Power BI - Part 6

Introduction

Well, it has been almost exactly one year since my last "Fun with Graphing in Power BI" article. I'm not going to lie, I had gotten a little burned out on all the maths involved. Maths is hard! Plus, I had kind of run out of ideas. But, then I was sitting around this past weekend and I remembered this project I did my senior year in college with Dr. Michael Rider. Dr. Rider had loaned me a book on computer graphics and I decided to write a simple ray tracing program as a kind of senior project. So I got to wondering. Could I pull off a ray tracing in Power BI? Well, turns out, yes...yes you can.

If you don't care to read the article and just want the PBIX file, click here.

Background

So what is ray tracing you might ask? Well, basically it is a computer graphics rendering technique used to generate an image by tracing the path of light as pixels in an image plane and simulating the effects of its encounters with virtual objects. It's probably best described with a picture to prevent 1,000 words being wasted.

Now, ray tracing does not have all that long of a fairly long history. It was first described in 1969 by IBM’s Arthur Appel. 10 years later, in 1979, Turner Whitted issued a paper called “An Improved Illumination Model for Shaded Display.” That guy now works for NVIDIA who are arguably are computer graphics geniuses. I know my son thinks they are as he is always bugging me about the latest and greatest graphics cards they produce.

The basic idea behind Arthur Appel's original ray casting algorithm is rather simple, shoot rays from the an "eye" or "camera" or "viewer", one per pixel, and find the closest object blocking the path of that ray. Using the material properties and the effect of the lights in the scene, this algorithm could determine the shading of objects in the scene.

Appel's algorithm traced rays from the eye into the scene until they hit an object, determining the ray color solely from the interaction with that object and a light source. Turner Whitted extended this concept by introducing recursive ray tracing. Whitted's algorithm generated up to three new types of rays, reflection, refraction and shadow. Each of these rays were traced and could impact the original ray as well as other rays within the scene. The overall effect was that recursive ray tracing added much more realism to ray traced images.

The Solution

The solution presented here is more of a ray casting solution than a ray tracing solution. And it is very, very basic. That being said, it was fun to put together. Another interesting aspect is that while most of my work in this series involved a lot of Power Query (M) code, this solution is purely DAX.

The first step was to define the basic components, the objects in the picture, the camera or viewer and the lighting. These were easily created with a few simple Enter Data queries. I chose to render a sphere as the algorithms for doing ray tracing with spheres are well understood and readily available. As a sphere is defined by a center point and a radius, I chose x=0, y=0 and z=10 as my center and a radius of 4. I positioned my viewer at x=0, y=0 and z=0.

I now needed a screen grid upon which to "shoot" rays. This was created by using DAX.

Grid = 
VAR __xTable = SELECTCOLUMNS(GENERATESERIES(-25,25,1),"__x",[Value])
VAR __yTable = SELECTCOLUMNS(GENERATESERIES(-25,25,1),"__y",[Value])
RETURN
GENERATE(__xTable,__yTable)

This created a table called "Grid" which identified the x and y coordinates of every "pixel" in a 50 by 50 grid centered around and origin of x=0 and y=0. The SELECTCOLUMNS statement is used to change the names of the columns returned by GENERATESERIES since GENERATE does not like having common column names between tables.

Once this was done, I could add a column to this table to determine if my rays "hit" an object in my scene.

Color = 
VAR __x0 = MAX(Viewer[vx])
VAR __y0 = MAX(Viewer[vy])
VAR __z0 = MAX(Viewer[vz])
VAR __x1 = [__x]
VAR __y1 = [__y]
VAR __z1 = 10
VAR __cx = MAXX(FILTER('Elements',[Element]="sphere1"),Elements[cx])
VAR __cy = MAXX(FILTER('Elements',[Element]="sphere1"),Elements[cy])
VAR __cz = MAXX(FILTER('Elements',[Element]="sphere1"),Elements[cz])
VAR __r = MAXX(FILTER('Elements',[Element]="sphere1"),Elements[r])
VAR __dx = __x1 - __x0
VAR __dy = __y1 - __y0
VAR __dz = __z1 - __z0
VAR __a = __dx*__dx + __dy*__dy + __dz*__dz
VAR __b = 2*__dx*(__x0-__cx)+2*__dy*(__y0-__cy)+2*__dz*(__z0-__cz)
VAR __c = __cx*__cx + __cy*__cy + __cz*__cz + __x0*__x0 + __y0*__y0 + __z0*__z0 + -2*(__cx*__x0 + __cy*__y0+ __cz*__z0) - __r*__r
VAR __discriminant = POWER(__b,2) - 4*__a*__c
RETURN
IF(__discriminant>0,1,0)

There is lots of math involved but it is based upon simple trigonometry. I essentially get the coordinates of my viewer, x0, y0 and z1. Then, I get the coordinates of my image plane, x1, y1 and z1. I chose 10 for my z value as this defined a plane at the same distance as the objects in my scene. Next I get the definition of the object in my scene, "sphere1" and return its center point and radius. Then there's the maths. If you want to know more about the maths, you can knock yourself out here, here, here, here and here. In a nutshell though, what the maths say is whether or not the ray "hit" the sphere. A discriminant of greater than 0 is "yes" and a discriminant of less than 0 is "no". A discriminant of 0 means that the ray is tangent to the surface of the sphere.

Once I had this column, I could plot it in a Bubble Chart visualization like so:

Yes, I know, not overly impressive, but it does mean that the maths worked. Yay...Maths! As further proof of that, if I change my image plane to be 5 instead of 10, I get this image:

As with the picture in the Introduction, the image of the sphere is now smaller because there are fewer pixels in the image plane for which the rays intersect the object. Similarly, if I set my image plane to be 50, I get this image.

We can also move our sphere around. Going back to an image plane of 10 I can define the sphere to be x=10, y=10 and z=10 and get this picture:

OK, so the basics of the ray tracing or ray casting were working. What I wanted to do next was try doing some type of lighting effect. This is basically the same formula but with a slight addition.

t = 
VAR __x0 = MAX(Light[vx])
VAR __y0 = MAX(Light[vy])
VAR __z0 = MAX(Light[vz])
VAR __x1 = [__x]
VAR __y1 = [__y]
VAR __z1 = 10
VAR __cx = MAXX(FILTER('Elements',[Element]="sphere1"),Elements[cx])
VAR __cy = MAXX(FILTER('Elements',[Element]="sphere1"),Elements[cy])
VAR __cz = MAXX(FILTER('Elements',[Element]="sphere1"),Elements[cz])
VAR __r = MAXX(FILTER('Elements',[Element]="sphere1"),Elements[r])
VAR __dx = __x1 - __x0
VAR __dy = __y1 - __y0
VAR __dz = __z1 - __z0
VAR __a = __dx*__dx + __dy*__dy + __dz*__dz
VAR __b = 2*__dx*(__x0-__cx)+2*__dy*(__y0-__cy)+2*__dz*(__z0-__cz)
VAR __c = __cx*__cx + __cy*__cy + __cz*__cz + __x0*__x0 + __y0*__y0 + __z0*__z0 + -2*(__cx*__x0 + __cy*__y0+ __cz*__z0) - __r*__r
VAR __discriminant = POWER(__b,2) - 4*__a*__c
VAR __t = (-1*__b - SQRT(__b^2 - 4*__a*__c)) / 2*__a
RETURN
IF(__discriminant>0,__t,0)

In this version, I compute __t, which is essentially the distance from the camera to the object. I defined a couple of additional columns so that I could create a standard of 10 "colors".

This involved a Rank column:

Rank1 = RANKX(ALL('Grid'[t]),[t],,ASC)

And a Lighted column:

Lighted = 
VAR __diff = (MAX([Rank]) - MIN([Rank]))
VAR __increment = DIVIDE(__diff,10,0)
RETURN
IF([Rank]=1,0,INT([Rank]/__increment))

With this, I was able to render the following image:

What this shows is that we are indeed dealing with a sphere. The pixels in the middle represent the surface of the sphere that is closer to the camera and those on the outside are farther away from the camera.

Conclusion

While it will never be in the top 10 list of things that you should be doing with Power BI and DAX...in fact...if there is a top 10 list of things that you should NOT being doing with Power BI and DAX, ray tracing is probably on that list instead. But, a rudimentary ray tracing process can be built rather easily in Power BI none-the-less and serves as an easy way to introduce yourself to the concepts of ray tracing.