This post is also available in: DeutschDeutsch

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, the first week of the new year is therefore divided in America: In Europe, the first week of 4 full days is defined as the first calendar week of the year: Calculation with the WEEKNUM function Basically, the calendar week can be calculated using the WEEKNUM function in DAX. The syntax looks like this: The first parameter <date> is the date. According to the official documentation, the <return_type> is either a 1 if the new week starts on […]
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. Dies konnte in Power BI Service liegen oder als Analysis Services Tabular Modell. This update changes just that. Ich kann mich live auf mehrere Datenmodelle gleichzeitig verbinden bzw. wie in DirectQuery auch noch um weitere Datenquellen wie flat files oder Web-Daten ergänzen.This means that I can run self service on my deployed dataset! I can combine and evaluate CSV files, new columns and even an additional […]
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 the individual tools, besides the FancyZones, is the Color Picker. Determining the exact color tone with the Color Picker In my work, I am constantly creating prototypes, proofs of concepts, and similar demos to show various customers what is possible with Power BI. Of course, the report should be customized to the customer as much as possible. This includes, naturally, using the customer’s colors. In the past, this was […]
This post is also available in: Deutsch Update March 2022: Screenshot of the new format pane added 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. The individual items should be displayed below each other at row level. The default view, when displaying only the Measures, is horizontally aligned: Even if you explicitly drag the measures onto the rows, they cannot be displayed at row level: There is, however, an option which is rather unknown, but which makes this possible very easily: the option “Show on rows” in the Matrix Visual: In the new format pane you will find the option in the following spot: As soon as this option is activated, the measures are automatically displayed on the X-axis: In my example, the use of custom visuals was not allowed. Otherwise, there are some other options as an alternative. […]
This post is also available in: Deutsch I recently received a tip from a colleague at work, which was also something I hadn’t seen before. More precisely, it was about integrating a Power BI report with all reporting options into a OneNote page. For this purpose, I use the report with the manual fonts as an example. The report has to be shared on Power BI Services (powerbi.com) via Share –> Embed report –> Publish to web (public): Warning: When sharing via “Publish to web (public)”, the report can be viewed by anyone who knows the link. This is highly dangerous and should only be done with reports that are intended for the public anyway! In the dialog that appears, you can specify a few options such as the size of the report. Then the report link can be copied: In OneNote, the copied link can simply be pasted and the report appears fully interactive and embedded in the OneNote page: Works only with Public Shared Reports However, there is one major limitation. At the current time, the feature only works with publicly shared reports. This means that anyone who is in possession of the link will be able […]