A Fine Slice Of SQL Server
Marco Russo and Alberto Ferrari don’t want to let any information slip out:
Let us start with a simple challenge: we want to show the percentage of sales in Europe, compared to the sales made to all customers worldwide. It is a relatively trivial question, the kind of DAX code you learn at the beginning of your Power BI career, and it can be solved with a simple measure:
But what happens when the model includes security roles? Well, that’s what you’ll have to read on to learn.
Comments closedDany Hoter gives us a reason to minimize use of many-to-many relationships in Power BI:
I must admit that in the last two years I’ve told many Power BI/Kusto customers not to worry about relationships that are created as M:M.
I was pretty sure that with Direct Query, such relationships are fine,
Indeed, the generated queries looked fine and performed as expected.
I recently became aware that the number of queries generated for some visuals e.g. Matrix and tables can be affected by the type of relationships between the participating tables.
Read on for a description of why you shouldn’t load your Power BI semantic models with many-to-many relationships, especially once Kusto is involved.
Comments closedAllison Kennedy checks the calendar:
Time Intelligence functions in DAX change the filter context on the Date table of your model.
Step 0: Prepare
Before using Time Intelligence functions, you should:
- Check out the documentation at https://dax.guide/functions/time-intelligence/
- Make sure your model has a Date table that’s marked as such: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
- Have a basic understanding of the CALCULATE function – it’s crucial to this solution
Read on for a four-step process covering how to apply a time intelligence function like DATEADD() or DATESYTD() in DAX.
Chris Webb shares some thoughts:
The name change proved to be surprisingly uncontroversial. Of course it’s very disruptive – trust me, I know, I have around 500 blog posts that I need to do a search-and-replace on at some point – so I have a lot of sympathy for people with books or training courses that need updating or who are getting calls from confused end users who are wondering where their datasets have gone. But there was a general consensus that the change was the right thing to do:
Read on for a bit more of the story, as well as some great links to get additional information on semantic modeling.
Comments closedReza Rad talks version control:
Power BI and Microsoft Fabric use Azure DevOps as the core component for their version and source control. The integration with Azure DevOps and Git helps maintain a copy of the source code and version it. It allows access to it anytime needed, plus the multi-development aspect added. In this article and video, you will learn how this integration works.
Click through for the article and video.
Comments closedOlivier Van Steenlandt sorts out a problem:
While I was writing another data recipe, I ran into an issue. For some reason, the “Create Calculation Group” was not visible / enabled in Tabular Editor.
I tried to create a Calculation Group by right-clicking on the Table Folder –> Create but the option wasn’t available.
I tried an alternative way: going to the Model Section in the Tabular Editor toolbar. But unfortunately, the option to create a new Calculation Group was disabled.
Read on to see how Olivier solved this problem.
Comments closedChris Webb puts on the lab coat and safety glasses:
Excel workbooks are one of the slowest data sources you can use with Power Query in Excel or Power BI. Reading small amounts of data from small workbooks is usually fast; reading large amounts of data from large workbooks can be very slow. But what about reading small amounts of data from large Excel workbooks? I did some tests and it turns out that performance can vary a lot depending on where your data is in the workbook and how that workbook is structured.
Read on for some interesting findings.
Comments closedOn the surface, when comparing Power BI vs Tableau, they appear very similar. Both are business intelligence tools. Both create slick dashboards and offer more than one data visualization tool. In fact, most people can’t distinguish between a dashboard created in Tableau and one created in Power BI.
Each of them has its strengths. However, their core difference lies in their approach to data integration, transformation, and analysis. Now, let’s break this down.
Read on for Rob’s take on the matter.
Comments closedOlivier Van Steenlandt covers a common case:
Many companies don’t follow the regular Calendar as we know (January 1st – December 31st). They follow their own Financial Calendar (often called Fiscal Calendar) which can start at any time of the year.
Because of this, writing Year-To-Date calculations in DAX for your Tabular Model might seem challenging.
In the step-by-step example, we are working for a company that starts its Financial Year on July 1st.
Read on to see one way to do it. It doesn’t quite solve the problem Olivier brought up, but I’d also make note that having a calendar table with fiscal + calendar year information in it helps remarkably well. It can even handle multiple fiscal year concepts; as an example, a state agency I worked for had a fiscal year on July 1 but the US federal government’s fiscal year begins October 1, so it was just a matter of having StateFiscalYear and FederalFiscalYear columns.
Also, check out Olivier’s new theming, under the Data Cuisine motif.
1 CommentKevin Chant checks in a Power BI project:
I decided to test the guide with the Power BI report that I showed in my post about work with Microsoft Fabric Git integration and multiple workspaces.
So, I went through the guide and was pleasantly surprised that it showed how to do it with a YAML pipeline in Azure Pipelines. Which I must admit I prefer for reasons that I covered why in a previous post about disabling classic pipelines in Azure DevOps.
Read on for a review of the issues Kevin had to sort out, as well as two mechanisms to improve the performance of your Azure DevOps CI process.
Comments closed