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,

This post is also available in: Deutsch

Measures are the best way to calculate your key figures in Power BI. In a matrix, however, these can normally only be displayed as values and not at row or column level. However, sometimes you need them exactly at the row level.

Display contribution margin calculation at row level

An example I came across recently was the presentation of a contribution margin calculation. I already apologize if the English terms are not translated correctly, my original calculation was in German ? . The individual items should be displayed below each other at row level. The default view, when displaying only the Measures, is horizontally aligned:

Standard display of measures in matrix in Power BI

This post is also available in: Deutsch

If you find a date or datetime format in UNIX format, the first thing you wonder is what it is all about or how you can display it “normally”. This is because the UNIX date format looks like this:

1604135115

What exactly is UNIX Timestamp?

To find a way to convert this, we first need to understand what the number means. The UNIX timestamp is the number of seconds since January 01, 1970. You can read exactly about why it is like this in the Wikipedia article. I want to focus on finding a

This post is also available in: Deutsch

In my first blog post, I briefly mentioned that my digital notebook – in the form of OneNote – is well filled with plenty of notes and approaches that I want to share here on the blog.

One category is DAX and M code snippets, which I use regularly. For example, creating a date or time table or creating ID columns from a date and other similar things. Simply approaches that you need again and again, and give you a bit of a headache every time you have to rethink them again.

So I came to the decision that I share them here on the blog as well. For this purpose I have created a separate page with all

This post is also available in: Deutsch

If you want to combine several tables, you have to ask yourself whether you should do this already in the data source such as SQL Server, in the processing of the data (ETL) in Power Query or in Power BI and, above all, where exactly there is a difference.

First of all, the post is relatively detailed, for all of those who consider it too long (tl;dr) here is the comparison table:

Duplicate entries remainDuplicate entries are removedUnequal number of columnsDeviating column namesDifferent order of the columnsCombine more than 2 tables
UNION in SQL ServerWith UNION ALLWith UNIONxx
APPEND in Power QueryxPossible with “Remove duplicates”xxx
UNION in DAXxPossible with “DISTINCT”xx
Overview of the different possible combinations

Data transformation in the data source or in the frontend?

A question that

This post is also available in: Deutsch

Automatic scaling of metrics or measures is possible in Power BI for just about any visual. However, there is currently no possibility to dynamically adjust tables or the matrix.

No dynamic scaling option for table and matrix

With this selection the values can be displayed in millions, for example.

Even if the values are restricted due to a slicer, filter or drill down, the manually set scaling is kept. Depending on the constellation, this leads to the strange situation that no more information can be obtained from the report because the values are too low for scaling.