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

This post is also available in: Deutsch

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: Without proper formatting, it’s hard to tell here what the formula is doing at all. If all measures in the report look like this, there is a big frustration. However, the Tabular Editor’s scripting engine provides a remedy for this. This can be started easily via the external tools: In the Tabular Editor, the following C# code can now be entered via the “Advanced Scripting” field: After a click on “Run Script” (green arrow) or a click on the F5 key the code will be executed. After that the changes have to be written back from the Tabular Editor into the opened Power BI file (Ctrl + S) or File –> Save and […]

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 of scattered between the columns of the fact table. But basically, you can also agree as a company to always use Measure Tables, which improves the user experience. Instead of picking a specific measure from the table below: …. is it easier to find a measure in the following table: What are the special characteristics of the Mesaure table? Including the just mentioned example of clarity, a Measure table has a few unique features: Always the first tableIndependent of the name of […]

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. Afterwards you get a good impression of how such a portal could look like: Integration of analysis and individual portal functions It is also very well solved how certain functionalities of a portal have been integrated directly next to the analysis. A new lead can be created via the “Add new Lead” function (unfortunately not in demo mode) and the leads from Power BI are displayed directly below. Source code of the portal available on Github Finally, here’s the best part. The source code for the entire portal is released at Github, so […]

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