What the fact.bi - All about Power BI, DAX, M, Power Query

This post is also available in: Deutsch

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 calendar week is returned with week start on a Monday. The syntax looks like this: Calculation with Power Query function Unfortunately, there is no hidden parameter in Power Query like in DAX, so we have to calculate the calendar week. However, Robert K. Bell has already done most of the preparation work. Based on his code, I modified the function a bit for our purposes. The following code you simply have to paste into a new query in Power Query: After that you only have to call the function as a new column with the parameter […]

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 […]

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. 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 […]

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 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 […]

Display DAX Measures in a Matrix on Rows

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. […]