Fun with Graphing in Power BI - Part 1

[Greg_Deckler]

Introduction

I am always interested in pushing the limits of Power BI and, as my recent entry into the 2017 Year in Review Contest demonstrates, I do not feel that working with Power BI always has to be...well...entirely serious. So, let's have some fun with Power BI and push the envelope a bit by tackling some problems that one might traditionally utilize a program like Mathematica to solve.

Recently, Microsoft released a couple of handy little functions, GENERATESERIES and SELECTEDVALUE. Ostensibly, these functions were released to support the new "What If" parameter functionality within Power BI Desktop. However, the inclusion of GENERATESERIES opens up a whole host of possibilities around easily generating the data to graph some really interesting mathematical functions.

So, if your goal in this is to learn about how to solve some kind of important data modeling problem or nifty, complex measure, this may not be the best article for you. But, if you want to look at some nifty visuals and perhaps learn a few things along the way, then you should enjoy this.

The Butterfly Curve

First up is the Butterfly curve (transcendental). Now, first of all, by transcendental, I do not mean of or relating to the spiritual or non-physical realm. In mathematics, the word transcendental means "(of a number, e.g., e or π) real but not a root of an algebraic equation with rational roots." And if you feel like the mathematicians seem to be overcomplicating things...well...you wouldn't be wrong, they are really good at that sort of thing. Second, I should mention that this curve was discovered by Temple H. Fay although it is apparently the only interesting thing that he/she ever did.

So, the Butterfly curve (transcendental) is often given by the polar equation:

If you don't know what polar coordinates are, no worries, you can learn about it here. But, in Power BI, we really do not have a way to plot polar coordinates. Thus, we need to convert this polar equation to Cartesian parametric equations. That's obfuscated math-speak for x and y coordinates.

And, importantly, this is evaluated:

So, to replicate this in Power BI, 12*3.141592654 is about 37.7. So, from the Modeling tab in Power BI Desktop, select "New Table" and use the following formula:

Butterfly = GENERATESERIES(0, 37.7,0.01)

This is creating a table from 0 to 37.7 in increments of .01. Rename the "Value" column to "t". Now, create a new column with the following parameter:

x = 
VAR e = EXP(1)
RETURN SIN([t]) * (POWER(e,COS([t])) - 2 * COS(4*[t]) - POWER(SIN([t]/12),5))

and another column:

y = 
VAR e = EXP(1)
RETURN COS([t]) * (POWER(e,COS([t])) - 2 * COS(4*[t]) - POWER(SIN([t]/12),5))

Now, create a Scatter Plot and drag "x" to the x-axis and "y" to the y-axis. Now, for whatever reason, Power BI tends to mess up the scale when you do this. So, switch the Scatter Plot to a Line Chart. Both x and y should be in Values, so drag x to the Axis. Now, flip the visual back to a Scatter Plot. x will be in Details and y will be in X Axis. Drag y to Y Axis and x to X Axis and then you should end up with a plot like:

Which is super cool, right?!?!

The Fermat Spiral

OK, let's try another. Fermat spirals. Fermat spirals were first discovered by Pierre de Fermat, who apparently actually did some other stuff as well, because there is an entire Wikipedia page on the guy. Fermat spirals are defined by the polar equation:

Ugh, polar notation again. "Why do mathematicians have to make everything so difficult?" you might ask. Because they hate you and everything else that is pure and simple and good. That's why. Mathematicians = Worst.

Alright, so for this one, create a new table and use the equation:

Fermat = GENERATESERIES(-100, 100, .2)

Now, rename the Value column to "theta". Theta? That's the zero with the line through it. It means the angle. Why don't they just call it angle? See the fact that mathematicians hate you above. Now, to calculate the radius, radiuses, radiusi? use the Power BI formula:

r = SIGN([theta])*SQRT(ABS([theta]))

I could have used POWER instead of SQRT, but taking something to the 1/2 power is the same as taking the SQRT so, I used SQRT. In both cases, you would have to wrap [theta] in an ABS (absolute value) because taking the SQRT or POWER of a negative number is muy malo and effectively impossible without introducing things like imaginary numbers, which; oddly enough, are a real thing and pretty much crucial for understanding how electronic circuits function. And before you start about how imaginary numbers can be real, see above regarding the fact that mathematicians hate you.

Now, we need to translate this from polar coordinates to Cartesian coordinates. The way we do this in general is to use the following formulas to add two additional columns:

x = [r]*COS(([theta]))

y = [r]*SIN(([theta]))*SIGN([theta])

And finally, add the following column for some flare:

Color = SIGN([theta])

So now we can create a Scatter Plot with [x] in the "X Axis" field, [y] in the "Y Axis" field and [Color] in the "Color saturation" field. Once you set the maximum color to that weird default salmon color and the minimum to that weird default cyan color you should end up with something like:

So, what you are seeing are concentric spirals with the positive values of theta in that weird default salmon color and the negative values of theta the weird default cyan color.

Now, there's a special form of this Fermat spiral that deals with the Golden Ratio. Ahhhhhhhh! That's the sound of a musical chorus saying "Ahhhhhhhh!". Anyway, this form produces a special result when r is defined in terms of whole numbers and when theta is a multiple of the Golden Angle..."Ahhhhhhhh!", 137.508 degrees. Man, that chorus is going to get annoying... The formulas, defined by H Vogel are thus:

Alright, so this time, create a new table like this:

FermatGR = GENERATESERIES(0, 300, 1)

Rename Value to n. Then create these columns:

r = 2*SQRT([n])
theta = [n] * RADIANS(137.508)
x = [r]*COS([theta])
y = [r]*SIN([theta])

The trickiness here to get the effect we want is to convert the Golden Angle...Ahhhhhhhh!...**bleep** it...to radians. What's a radian? See mathematician hate above. You should know the drill by now, Scatter Plot, x on the X Axis, y on the Y Axis. Poof!

Now, let's get fancy. This is supposed to be about the Golden Ratio/Angle...Ahhhhhhhh!...right? Well, we all know from reading Dan Brown novels that the Fibonacci series is all about the Golden Ratio...Ahhhhhhhh! So, let's create a Fibonacci series. Create a new table with this formula:

Fibonacci = GENERATESERIES(0,20,1)

Now create a column with this formula:

Fibonacci =
VAR n = [Value]
VAR GoldenRatioAhhhhhhhh = (1+SQRT(5))/2
VAR Part1 = POWER(GoldenRatioAhhhhhhhh,n)
VAR Part2 = POWER(-1*DIVIDE(1,GoldenRatioAhhhhhhhh),n)
VAR Numerator = Part1-Part2
VAR Denominator = SQRT(5)
RETURN DIVIDE(Numerator,Denominator)

Now you have a table with the Fibonacci series in it. Go back to your FermatGR table and create this column:

Color = IF(CONTAINS(Fibonacci,Fibonacci[Fibonacci],[n]),1,0)

Thus, if [n] is contained in the Fibonacci series, then it will be a 1, otherwise, 0. Plop Color into your Color saturation field in your visual, set the minimum to that weird cyan color and the maximum to that weird salmon color and wahla!

As you can see, the Fibonacci numbers converge around the x-axis, which is cool if you are into that sort of thing. What you are actually seeing is how sunflowers work. By the way, sunflowers are not actually flowers, they are actually inflorescences. Why? Because biologists also hate everyone, just like mathematicians. In any case, in sunflowers, the mesh of spirals occurs in Fibonacci numbers because divergence (angle of succession in a single spiral arrangement) approaches the golden ratio. What? Just go with it, you are seeing the convergence of the Fibonacci numbers around the x-axis because of what was just said above that I dare not repeat.

Conclusion

Well, that's all for Part 1 of this article on Fun with Graphing in Power BI. Stay tuned for the next installment where I will cover graphing equations of things like kidneys, muffins, plants and more! In the mean time, try changing the parameters of your table for the Fermat graphs like the minimum and maximum numbers, size of the increment, slightly different values of theta, etc. You can generate some rally wicked stuff.

Posts in this series: 1, 2, 3, 4, 5