COVID-19 current vaccination numbers for the canton of Basel-Stadt, Switzerland

This post is also available in: Deutsch

This past weekend, I had an aha moment. My girlfriend asked me what the current status of COVID-19 vaccination was here in Switzerland in our area, which means in the canton of Basel-Stadt. After a short internet research, we found either newspaper articles that were already a few weeks old or statistics that presented the current figures only insufficiently or in a complex way.

The canton of Basel-Stadt provides all data as CSV and JSON on its Open Data Basel-Stadt website and allows the use under the Creative Commons license.

Current vaccination status as Power BI Report for Basel-Stadt

As a Power BI enthusiast, I immediately thought of presenting the whole thing in

Trigger automated workflows directly and with filter context in Power BI Report with Power Automate Visual

This post is also available in: Deutsch

Last Friday, Microsoft released the latest Power BI update a little earlier than usual. The highlight from my point of view is the Power Automate Visual ( previously Microsoft Flow), which works with the now released April 2021 version of Power BI

What is Power Automate?

With Microsoft Power Automate it is possible to combine tasks and workflows with different applications in order to automate them. Over 300 services are now supported, for example Dropbox, Outlook, Power BI, Sharepoint, Twitter, SalesForce, etc.

Application areas can be simple notifications, but also very complex and highly automated workflows.
A few examples could be:

When a new record is added to a SQL Server table, push it to a Power BI Live Dataset
If I got linked on

Automatically filter report on actual date with calculated column and with measure

This post is also available in: Deutsch

Sooner or later, every Power BI developer will face the question of how to automatically filter a specific visual or the entire report to today’s date.
This can be done in two ways:

With a Calculated Column (always works)
With a measure (works in specific cases)

The approach with a calculated column is much more stable, since measures generally cannot or should not be used as filters. However, if the date exists in the filter context, then this is also possible with a measure, which is why I will present both alternatives.

Filter to today’s date with calculated column

As just said, filtering by calculated column is the more stable of the two approaches. Here, a new calculated column is created in the date dimension, where it

Automation in Power BI – Format all measures, replace text, set default summarization and more

This post is also available in: Deutsch

One topic that I think doesn’t get enough attention is the ability to automate work in Power BI with the Tabular Editor.

Especially the official documentation gives some nice insights into what the scripting engine can achieve.
I would like to present a few examples in this article.

Example 1: Automatic formatting of all measures

If you often work with Power BI, you know the situation when you open a report and all measures look like in the following picture:

Unformatted formula in DAX — Unformatted

What is a Measure table and how can I create it properly

This post is also available in: Deutsch

Although the feature has been in Power BI for a while, Measure Tables are not used enough in my opinion. Also during my trainings or in conversations with customers, I often notice that the feature has not really reached the minds of the report developers yet.

What is a Measure Table and what does it look like?

A measure table is an independent table that does not contain any columns and only the measures themselves:

When should I use a Measure table?

I personally use them when I have more than 4-5 measures. I find it more clear to have a large number of measures in a separate table instead

Understanding Power BI Embedded with Source Code and Contoso Example Portal

This post is also available in: Deutsch

Power BI Embedded is a great way to integrate entire reports or specific elements from a Power BI reports into a company portal. Unfortunately, this is not always easy to explain and you need some imagination to grasp how this might look in practice.

Power BI Embedded as a demo portal

However, this has recently changed as Microsoft has released a demo portal for their imaginary company Contoso. On the following website you can find a great example how to use Powe r BI Embedded in an enterprise portal.

Important here, the portal must be entered in demo mode.

Login to Contoso Power BI Embedded Portal via Demo </div>
</div>
</div>
<div class=

Calculate ISO 8601 week in Power Query

This post is also available in: Deutsch

Last week we already dealt with the topic of the correct calculation of the ISO week in DAX. Since the question came up about how to solve this in Power Query, I will look into it this week. For the problem with calendar weeks, I would like to refer to last week’s post.

Calculation of US calendar week with Date.WeekOfYear function

Like in DAX, there is already a function in Power Query to calculate the calendar week. This is called Date.WeekOfYear and returns the US format with week start on Sunday. With an optional parameter, the

Calculate ISO 8601 week in DAX with hidden parameter

This post is also available in: Deutsch

Happy New Year to all my readers!

The first challenge this year is the correct calculation of the calendar week. Because this year we have again the case that the calendar weeks differ between America and Europe.

Different calculation of the calendar week between North America and Europe

While in North America the first week of the new year is always defined as week 1, Europe follows the ISO 8601 standard. According to ISO calculation in Europe and Asia, the first week in the new year, which contains 4 or more days, is defined as week 1. In the USA and Canada, there may also be a half week as week 1.

In 2021,

Power BI Composite Model V2 – the missing part between Self-Service and Goverened BI is in Preview

This post is also available in: Deutsch

Normally, I write about updates in the Power BI universe quite rarely, and when I do, it’s only when it’s a significant improvement from my perspective. So it is in this case.Last week, Microsoft introduced in Preview the ability to access Power BI datasets and Azure Analysis Services via DirectQuery. What doesn’t sound all that spectacular could actually be Power BI’s next big unique selling point!

Combination of multiple Power BI datasets and flat files

But what exactly are the new possibilities of the new composite model and what does that mean in real life? So far we have been able to connect “live” to multiplerelational databases. Once we connected to a Power BI dataset, there was only the option to connect to a single dataset.

Tip for your daily work – Do you already know Microsoft PowerToys and the Color Picker?

This post is also available in: Deutsch

Today I’ m going to write a post that doesn’t directly have anything to do with Power BI, but still adds a lot of value there. For about a year now, I’ve been using Microsoft PowerToys, a combination of different tools that make your life easier.

Original idea, FancyZones to divide the screen into different areas

Actually, I came across PowerToys when I was looking for a tool that can divide my wide-screen monitor into different areas. I tested a few tools and ended up with FancyZones from the PowerToys Suite. From my point of view, it works the best of all tools and at the same time it is also free of charge.

However, what I use the most after trying out