In this blog post we’ll build a Power BI Report by standing on the shoulders of giants.

If you work with Excel most of your day and you have not heard of Daniel Ferry then I strongly suggest you head over to Excel Hero. The site has not been active for some years now but that really doesn’t matter. What you will see on Daniel’s blog will change your view of what Excel is and what it can do.

One of my favourite blog posts is the Excel Lissajous Curve Explorer. Daniel shows a very elegant way to implement Lissajous Curves, mainly through the use of Named Formulas and not much else.

Please do read the post, as it explains Lissajous Curves and what we need to do much better than I could.

So, I asked myself, could I recreate this in Power BI? Let’s see….

**SPOILER ALERT**: Yes, yes I can.

TL;DR: Please have a play with the report and let me know what you think. I will post a follow up soon, as I’m not 100% happy with Power BI Scatter Charts, so I’ll write about the R Script Visual.

Otherwise, below, I go through the steps taken to create this report, some of the lessons learnt, and things to do in the future.

#### Starting Off in Power BI

First off, we’ll create n, the number of data points we want. Daniel uses 4000 and so shall I.

Go to the Modeling ribbon and click ‘New Table’:-

n =

GENERATESERIES ( 1, 4000, 1 )

This gives us a table with the numbers 1 to 4000. Let’s rename the column to ‘n’ and add a ‘New Column’, which we’ll call ‘t’, to work out ‘t’:-

t =

( ‘n'[n] – 1 )

* 2

* PI ()

/ COUNTROWS ( ‘n’ )

So far, so easypeasy. However, that’s when I hit a road block. I added columns for ‘x’ & ‘y’, with the appropriate formula, and a couple of Slicers to represent ‘a’ & ‘b’ values.

Long story short: I went down a route that meant that ‘x’ and ‘y’ were always static – created at the time the table ‘n’ was created. The Slicers did nothing to the table ‘n’ – even if I changed the values and then ‘refreshed’ the data – and the chart needs to be interactive, so some more research was required.

But, let’s go back to the Slicers.

#### Slicing & Dicing

Under the Modeling ribbon, click on ‘New Parameter’, in the ‘What If’ section.

We can change the Name to be ‘a’, the Data Type to Decimal Number, Minimum to 1, Maximum to 100, Increment to 0.1, Default to 1.

We’ll leave the ‘Add slicer to this page’ ticked, so that it adds a slicer.

Do the same again, but this time the Name of the ‘New Parameter’ should be ‘b’.

And we have two Slicers to dynamically change the values of ‘a’ & ‘b’.

You see that two new tables are added and within them is a column ‘a’ showing rows going from the min to the max, in the appropriate increments, that you entered. A measure is also added that holds the currently selected value in the Slicer. For the example of ‘a[a Value]’ it is defined as:-

a Value =

SELECTEDVALUE ( a[a], 1 )

We’ll come back to the function SELECTEDVALUE later, but I thought it worth mentioning here.

So, I had a table that ‘worked’, in that it would show a Lissajous Curve when plotted on a Scatter Chart and Slicers that would allow me to change the value of ‘a’ & ‘b’ but I did not have a method of linking the two.

I’d figured, in my head, that ‘x’ & ‘y’ would have to be Measures – that I could write some DAX that would take the Slicer values. But, also, in my head, was the thinking that Measures could only a single result, whilst I needed 4000 ‘x’ & ‘y’ coordinates.

#### A New Hope

Lady Luck must have been smiling on me, as I happened to be watching the Power BI YouTube Channel webinar, ‘A walk through of Power BI most advanced features through Star Wars data – Gil Raviv’. [I’ve linked to where Gil talks about the technique he uses]. Gil was showing a method of using a measure that iterates over a table and I realised that this is what was required for my Lissajous report. So, I noted down the DAX and a reminder to email him and say thanks.

This is the DAX he used:-

AVERAGEX (VALUES ( <table> ), <expression> )

Now we can add Measures to the ‘n’ table for the ‘x’ & ‘y’ values that are now linked to both the table and the slicers.

Make sure you have table ‘n’ selected and click ‘New Measure’. We’ll call this one x_DAX:-

x_DAX =

AVERAGEX ( VALUES ( ‘n’ ), 1 * SIN ( a[a Value] * ‘n'[t] + PI () / 2 ) )

And we’ll do the same to create y_DAX:-

y_DAX =

AVERAGEX ( VALUES ( ‘n’ ), 1 * SIN ( b[b Value] * ‘n'[t] ) )

Now we can add in a Scatter Chart and play with the Slicers. Here are a few examples:-

Basically, that is working. But, Daniel included a Gallery of values for ‘a’ & ‘b’ that showcase some interesting curves. So let’s look at that.

#### Galerie d’art

We need some way to ‘Take the numbers from the Slicers’ or ‘Take the numbers from the Gallery’, and another Slicer is the obvious way.

We can’t use ‘New Parameter’ as that only accepts a numerical value. We’ll have to do the old fashioned way.

Let’s click ‘New Table’, under the Modeling ribbon and enter:-

Source =

{ “Slicers”, “Gallery” }

Rename the column to be ‘Source’.

Back on the report, we can add a Slicer and connect it to Source[Source]. After a bit of formatting we get:-

We now need to get the values that Daniel used in his Gallery. Perhaps there’s a way of writing some DAX that would produce a table with interesting Lissajous Curves, but that is beyond this post (and… erm.. beyond me – I mean, I recall using things like Lagrangian mathematics to optimise financial calculations back at University but I think that part of my brain is currently being used to store quotes from Rick & Morty).

So, let’s use the easy way and call on the House of M.

On the Home ribbon, click ‘Get Data’ and we’ll choose from ‘Web’. We can then paste in the direct link to the Excel workbook on Excel Hero. The Gallery values are most of the very few worksheet values in the workbook (remember Daniel is presenting an elegant way to chart using Named Formulas, so there really isn’t much in the worksheet [and yet there is so much to learn]).

We could go through the steps to import the data but the code for M is:-

1 2 3 4 5 6 7 8 9 10 11 12 |
let Source = Excel.Workbook(Web.Contents("http://www.excelhero.com/blog/workbooks/LissajousExplorer_excelhero.com.xlsx"), null, true), #"1_Sheet" = Source{[Item="1",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(#"1_Sheet",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", Int64.Type}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type text}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column28", "Column29"}), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", type number}, {"b", type number}}) in #"Changed Type1" |

Update the Name of the query to ‘Gallery’ & click ‘Close & Apply’, and we then have a table called ‘Gallery’ with all the values for ‘a’ & ‘b’.

We can then add a Table Visualization to our Report for the Gallery, ensuring we update the Values field of ‘a’ & ‘b’ to ‘Don’t Summarize’ by clicking on the down-arrow (otherwise the values of ‘a’ & ‘b’ will be summed):-

NOTE: Try not to click on the column titles for ‘a’ or ‘b’. If you do then the table gets sorted asc/desc, depending on how many times you’ve clicked. You could always add an Index Column to the table when you get the data. That would allow you to sort the table back to its original state. But, it’s no biggy – the chart will still work however the table is sorted on the Report.

To use the Gallery we need to click on one of the rows. This will then filter the table and we can use the ‘a’ & ‘b’ values of that row. To do this we use a DAX function we came across earlier:-

SELECTEDVALUES (<columnName>[, <alternateResult>] )

The <alternateResult> argument is optional but in this case extremely useful to us. When the whole table is selected, i.e. we haven’t filtered on a particular row, then SELECTEDVALUES returns BLANK(). Our formulas for x_DAX & y_DAX will not like BLANK() values being passed to them, so it’s useful that we can define an alternate result, which we’ll set to 1. Then, if we choose ‘Gallery’ but haven’t selected a row, our chart will default to a circle, as both ‘a’ & ‘b’ will equal 1.

So, we’re going to create two new measures and do something like:-

a =

SELECTEDVALUE ( Gallery[a], 1 )

But, we’re going to wrap this in a SWITCH(TRUE(),….) combination, so that we can take into account which Source (‘Gallery’ or ‘Slicers’) has been selected.

Ensure the table ‘n’ is selected, click ‘New Measure’, and enter:-

a_selected = SWITCH ( TRUE (), Source = “Gallery”, SELECTEDVALUE ( Gallery[a], 1 ), Source = “Slicers”, a[a Value], 1 )

And create another ‘New Measure’ and enter:-

b_selected =

SWITCH (

TRUE (),

Source = “Gallery”, SELECTEDVALUE ( Gallery[b], 1 ),

Source = “Slicers”, b[b Value],

1

)

Now, it doesn’t matter which Source we choose; a numeric value will always be passed to x_DAX & y_DAX. We just need to update the formulas for those two measures.

x_DAX gets updated to:-

x_DAX =

AVERAGEX ( VALUES ( ‘n’ ), 1 * SIN ( n[a_selected] * ‘n'[t] + PI () / 2 ) )

y_DAX gets updated to:-

y_DAX =

AVERAGEX ( VALUES ( ‘n’ ), 1 * SIN ( n[b_selected] * ‘n'[t] ) )

Congratulations! You can stop there, as that’s the ‘engine’of the report done.

#### Power BI Lissajous Curves Explorer Final Touches

I wanted to continue to make the chart look as much like Daniel’s as I could. So, after formatting the chart, adding some images, adding some Text Boxes, and adding some Card Visualizations, I got:-

You really don’t need to add the images, Text Boxes, etc. but if you have the time, and more importantly, the patience then it’s worth it in the end, as:-

- You learn techniques to add more information to charts
- You understand how long it can take to move these objects around the screen and get them set up looking correctly

#### Power BI Lissajous Curves Explorer: Final Thoughts & Follow-up Posts

- I’m happy with the table ‘n’ but I was always looking for a way that the value of ‘n’ could be changed dynamically. Is that possible?
- As I say above, I’m not 100% happy with Power BI Scatter Charts.
- The data point options are too large for most of the Lissajous Curves and do not look as refined as their equivalents in Excel.
- It would be good to have more control of the data point format
- In some cases, turning on High Density Sampling makes the chart slightly better
- However, some do look pretty cool – like images of bioluminescent, deep-sea jellyfish or a virus.

- I have a version of the Report that uses a R Script Visualization next to the Scatter Chart Visualization. I will write a post on that soon.
- In the SELECTEDVALUE & SWITCH functions I have set the default to 1 for ‘a’ & ‘b’. That works fine, but ideally I’d like to hide the chart under certain conditions:-
- Source = “Gallery” but no row selected
- Source has both options selected. (Try it – you’ll get an error)
- If we were in an Excel/Power BI mash-up then some pseudo-VBA/DAX like this might work:-
- IF Source = “Gallery” AND ISBLANK(SELECTEDVALUE(‘n'[a_selected])) THEN Chart(‘Lissajous’).Visible = FALSE

## 1 thought on “Power BI Lissajous Curve Explorer”