Sticker Charts

13 August 2023

Reporting #

For a large amount of my life I didn't really measure much. I'm bad at arts and crafts because I don't properly cut at the right angle. When I volunteered on farms I'd plant in weird lines. I like to cook a lot more than I like to bake. I’ve always considered myself an “artistic” person more than a “science” person.

Now measurement, numbers and reporting are literally my job at a data-obsessed company. I've been building reports for HR data around recruitment, headcount and attrition, and training data.

It turns out people find data really useful. And by people I mean business people. You need data to see what's working, where things are going, how people are doing at their job. You need data to make the number go up - usually the number is "profits".

Power BI #

There are a number of reporting tools out there. There's Apache Superset, there's Tableau, but the most ubiquitous is Power BI. Power BI is a reporting offering from a certain little company in Seattle. It's is available with a product they sell called Microsoft 365; a SAAS product most businesses already have a subscription with. So it's a no brainer for these businesses to use Power BI. They're already paying for it.

Power BI is a pretty classic Microsoft product: absolutely packed with features thrown together in the most haphazard way you can imagine. It's easy to get started, with but once you get off the beaten path you start to feel bounds of the walled garden.

My 3 big Gripes with Power BI #

As a developer coming to Power BI there's a few big things that annoy me:

Bad Code Editing Experience #

When you want to write a DAX measure or do something custom with M Query on the desktop app you'll find yourself in Power BI's text editor. It has some basic autocomplete, and some small amount of syntax highlighting but that's it. There's no Control-F, there's no way to look up definitions of functions, there's no formatter, there's no way to customise the editor theme in any way.

I’ve had to literally copy and paste an M Query snippet to notepad so I can control-F throught to check whether I'm including a column. You know something is wrong when notepad has a feature your text editor doesn't have.

2 Different Proprietary Languages #

There's 2 different languages Power BI uses: M query and DAX. It's important to have a deep understanding of both if you're doing anything nontrivial in Power BI reports. I'll accept Microsoft will be Microsoft and is allowed to create new proprietary languages, knowing full well that my knowledge of JavaScript, C# and SQL becomes useless in this environment. But what really annoys me is they created 2 new proprietary languages for a single product, and both have completely differing syntax.

For instance in DAX, we have the magical operator “IN” to check if something is in a list:

    NewColumn = IF(
        'Table'[OriginalColumn] IN {"String1", "String2", "String3"},
        TRUE(),
        FALSE()
    )

Whereas in M query we have to use a “List.Contains” function:

     NewColumnTable = Table.AddColumn(
        Source, 
        "NewColumn", 
        each List.Contains(
            {"String1", "String2", "String3"}, 
            [OriginalColumn])
        )

In DAX we have the logical operators && and || for “and” and “or”. Whereas in M Query we use the words and and or for the same task. Wouldn’t it be better for the syntax to be consistent when it’s part of the exact same platform?

Lack of Smart Caching Behaviour #

When you're working on a Power BI dataset, you write your query then it will get pulled it down from whatever source you use (whether that be an SQL server, a Power BI dataflow, a web service or any number of other sources). Then you can add steps to process the data - removing and renaming columns, adding types to them, merging with other queries etc.

The problem seems to be that there's no separation between the grabbing of the data and the processing of the data. And this will mean that every time you change your M Query it will need to redownload your whole data source. This can take up to half an hour with the dataset sizes I've been working with. There's no way to opt out of redownloading all the data to say "no, I don't care that this isn't fresh". No, it will be a half an hour iteration to check whether your code works.

Good Things About Power BI #

I could go on about things that annoy me about Power BI, but some of the good things:

When you're following the happy path, Power BI is a super quick way to throw a report together. It comes with a lot of default visuals and there’s also an ecosystem of third party visuals. Microsoft forums have a lot of advice on different problems. Also, sometimes the low-code features can save you a lot of time writing code. There’s also permissioning tools, usage analytics tools, and a bunch of different integrations with other Microsoft products of course.

Sticker Charts #

Working on these reports also make me wonder whether I should start measuring more things in my life, and presenting these measurements in a visual way. Having a visual way to see your progress in something makes that progress feel more real. This is the reason senior leadership teams and business people are so into reports.

It’s like a kid’s sticker chart: a kid does something right and they get a sticker on their chart. They feel the endorphins of seeing what they achieved, and it makes it a lot easier to do the next time because they had a visual symbol that they did well. I feel like I could use reporting to encourage me to get out of my default behaviour (endlessly scrolling down a social media app). And maybe do something better, like learn vim or run up a hill.