Unlocking Insights: An Introductory Guide to Integrating Jamf Pro and Microsoft Power BI for Powerful Reporting - Part 6

Part 6 – More Visualizations

By the end of our last post, we had Power BI set up to ingest data from a JSON file stored in a SharePoint site. I showed you how to create a straightforward visualization that simply represented our data in a table. In this post, I will show you one way to make a Measure that can represent how many devices are running a specific family of macOS.

Create a Measure

The first thing we need to do is create a Measure. While there may be other, better ways to gather this information together, this is simply the method I found that worked for me.

You can click on the New Measure button in the toolbar or right-click on your query (mine is “computers”) and choose New Measure.

You will notice that the toolbar changes to show the Measure Tools toolbar and a small input bar for entering our measure. If you click on the disclosure triangle at the right of this input bar, you can expand the window to provide more room.

You can see that the default name “Measure” is already selected and that there are the beginnings of a query in the box with “Measure =.” You guessed it: If we change the word “Measure,” we will rename this query.

We will start by capturing all devices running the latest beta version, macOS 15, or macOS Sequoia. We will use the COUNTROWS function and the FILTER function and do a little LEFT work on the OS versions. Here is the full DAX query that we will use:

Sequoia = COUNTROWS(
// https://community.fabric.microsoft.com/t5/Desktop/DAX-Starts-with/td-p/1594476
    FILTER(
       computers,
       LEFT(computers[inv.Operating_System_Version], 2) = "15"
    )
)

Let me break this down a little. We’re telling DAX to “count all of the rows in a dataset that we provide.” The dataset we are providing is a filtered version of our computer’s query, filtered by the value of the left 2 characters in the Operating System Version column. I hope that makes sense to you. If it does not, leave a comment or reach me on Slack, and I’ll be happy to try to clarify.

Paste that DAX query into the input area and then press return. Once you do that, you will notice the name in the menu bar changes to Sequoia and you have a new measure under your query in the Data tab on the right.

You can create more Measures to capture other macOS versions, changing the name before the = sign and the numbers on the LEFT line. For older versions of macOS and OS X where the versions all began “10.”, you will want to adjust the LEFT statement to look at the first 5 characters and set the numbers to 10.XX, replacing XX with the version number. Here’s an example of looking for macOS Catalina:

Catalina = COUNTROWS(
// https://community.fabric.microsoft.com/t5/Desktop/DAX-Starts-with/td-p/1594476
    FILTER(
       computers,
       LEFT(computers[inv.Operating_System_Version], 5) = "10.15"
    )
)

Hopefully, you do not have devices that are much older than that in your environment. 🙂

Visualizing Measures

Card

Now that you have all of your measures created, what can we do with them? We can add them as display cards to show the numbers, or we can create bar graphs or pie charts to visualize our distribution of versions.

We’ll start with a Card visualization. Click on the Card visualization in the list of visualizations to add it to our report.

Now locate the Measure you want to do first and drag that into the either the Fields section of the Visualizations tab, or directly into the card on our report.

You should now have a card that is showing you how many devices in your dataset are running whichever operating system measure you chose. In my case, I created a measure for Sequoia and I have 6 devices running macOS 15.

I went ahead and created measures and cards for a few different versions of macOS and my dashboard looks like this:

Getting those cards to line up and look uniform is pretty simple. If you click on one of the cards you will see on the Visualizations tab three icons above the different visualizations. Click on the middle icon, the one that looks like bar graphs with a paintbrush, and you will be able to change the properties of the card, like position and size. Come up with a size and position of your first card, and then use those numbers to set the other cards.

Pie Chart

Let’s make it a little easier to visualize the OS versions in our environment and use a Pie Chart. Click on the pie chart visualization to add it to our report. Now check the boxes next to each of your measures to add them to the pie chart.

Just like with our Card measure, you can use the formatting section of the Visualizations tab to adjust the pie chart look and feel. You can change the title of the pie chart, adjust the labels next to each slice, and more.

What’s Next?

The one question we haven’t answered yet is this: “How do we get this in a place where others can view the report/dashboard?” Well, our next post will cover how to do just that, including how to setup automatic refreshes of the dashboard information.

The Geeky Gordo