Items NOT Selected in a Slicer?

I recently attend a school reunion (class of 1981 for what it’s worth – wow, I’m old!).  We all had a great time catching up, looking at old photos, taking new photos etc – how things have changed!  Anyway that got me thinking that it would be great to do something cool with Power BI and my class photos.

I don’t want to share those photos on my blog without asking every single person for permission, so I have decided to replicate the idea using the Simpsons (without their permission).

Click a Slicer and See that Person

My idea was that I would load school photos and also the reunion photos onto the one page.  The user can then click on a slicer with someone’s name (or any other information about people) and “see” those people highlighted in the photo.  I started thinking that I could use the excellent Synoptic Panel from The Italians for this.  The only problem I could foresee was that Synoptic Panel is designed to provide shading over an image based on what was selected.  I wanted to shade/hide those people that were NOT selected.  Anyhow, I love a challenge.

This is What I Built

Installing Synoptic Panel

First I installed the Synoptic Panel custom visual.  The Microsoft Store is now embedded in Power BI, so I used the menu items as shown below.

  • I clicked on From Store (#1 below)
  • Typed in ‘syn‘ (#2, short for Synoptic Panel)
  • Clicked search, and got nothing (#3)

image

When I typed in ‘synoptic‘ and searched again, it worked this time.  Mmm, a bit of work to do there Microsoft!  I’m pretty sure your major search engine competition would have found Synoptic Panel for me if I searched for ‘syn‘ in a list of less than 1 hundred items.

image

Anyway, I installed the custom visual as shown above.

Now, how to use it?

I was a bit confused how to use Synoptic Panel at first actually.  I expected to quickly and easily see a way to import a picture, but I actually had to read the documentation to find out how to do it.

In short, you need to:

  1. Load a table of matching data.
  2. Place at least 2 items into the Synoptic Panel Fields section card.
  3. Then and only then can you see the menu to load your image.  (It would be good if the menu was visible from the start, even if it provided a warning something like “add data to fields list before importing image”.

image

Anyway, all good.  Time to draw the selectable shapes over my image.

Create an Image

I checked out the online http://synoptic.design tool.  This was pretty good, but really only helpful if you have shapes with up to 4 points.  In my case I was trying to trace people, so 4 points weren’t going to be enough.  Anyway, I worked out it was an SVG file with embedded vector coordinates.  So I did what I always do when I need something like this done, I asked my daughter Maddie to do it for me (she is a graphic designer Smile ).    Maddie used Adobe Illustrator, loaded the image and then drew the borders.  You have to name the vectors in the SVG and they have to match to the data table you are going to use.  We named the vectors to be the name of the characters. Maddie is a freelancer – contact me if you want her to do some work Smile.

Load the Data Table

As mentioned above, you must load the data table before the image can be loaded.  This is what I loaded in the data table (using Enter Data).

image

I then wrote a single measure

Selected = COUNTROWS(Simpsons)

Load the Image

After adding at least 2 items to the fields list, I was able to load the image using the embedded menu shown earlier in this post.

image

Add a Slicer

I added a slicer and clicked on it to select one of the characters.

image

Select (Conceal) Items NOT Selected in the Slicer

What I wanted now was to cover those items NOT selected in the slicer, and show those that ARE selected.  I wasn’t sure how to do this to start with, but a bit of trial and error (and Dr Google) I came up with the following solution.

  • I duplicated the table of names and called the new table data.  I needed to do this otherwise I couldn’t find a way to remove a filter coming from a slicer while using a single table (let me know if you can work out how to do that).
  • I joined the tables as shown below.  The Simpsons table is the Lookup Table and the Data table just has a list of names.

image

  • I wrote the following measures:
    • Selected = COUNTROWS(Data)
    • Not Selected = CALCULATE(COUNTROWS(Data),(EXCEPT(ALL(Simpsons),Simpsons))).
      • The EXCEPT function is the secret sauce here.  It takes an unfiltered copy of the table ALL(Simpsons) and compares it to the filtered copy (the second parameter).  The resulting table contains all those items in the first table that are NOT in the second table. The second table respects the initial filter context, hence the new table contains the anti-join.  Of course Lineage applies here with this new virtual table, so this new virtual table contains those items NOT selected will filter the data model inside the CALCULATE.
  • I then changed the set up of the Synoptic Panel visual as follows:
    1. I put Data[Name] into Category (#1).  Note, this is the data table, not the lookup table
    2. Placed [Not Selected] as the Measure (#2)
    3. The slicer is Simpsons[Name] (#3).
    4. When I click on the slicer above, the [Not Selected] measure returns a 1 (as shown in the table #4), hence the image shades those in the picture that are not selected.image

Any Slicer Works

Of course, like any lookup table, I am able to add slicers on any column and see all the characters that match.  You can play with the live interactive version below.

School Photos

As a reminder, the purpose of this idea was to load multiple school photos along with photos of us all 30+ years down the track.  You can use this technique on as many images as you like.  Click on a name to see a before and after, or any other meta data you have loaded.

Share?

Comments

  1. Brilliant as ever. I have a question for you though. How did you embed the Power Bi canvas? The ability to share through embedded code was removed on 18/1… so how are you doing this?

  2. Awesome Matt ! This is a great visualisation technique with lots of potential in business applications. Also love your yami sauce of EXCEPT function,- wondering if a construct of ALLEXCEPT can be used here too?

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x