In practical business, data iteration is widely used, such as present value or depreciation. However, as we all know, DAX is a functional language based on column engine, each row of data is calculated based on its own row context. But sometimes ...
I often get asked about best practice on when to use a lot of smaller measures that refer to each other VS when to write the entire expression as one long measure. If you have a great answer to this question, I'd love to hear your perspective, but this post provides one example of how lots of smaller measures can sometimes come in handy.
Power BI Time Intelligence provides powerful functions to deal with Year, Quarter, and Month. But WTD and this Week vs Last Week do not have any out-of-the-box solution. Let’s quickly deal with Week Time Intelligence.
Power BI supports a functional language called DAX (Data Analysis Expressions), which basically represents an executable piece of code inside a function. DAX expressions can sometimes be difficult to use and understand. There are multiple DAX expressions out there, but today I will be concentrating on a problem statement for using the RANKX function, and how to resolve it.
There are situations in which you want to use a ranking of rows based on one column, but that column has non-unique values. Sometimes, there are other columns you want to use, if two rows have the same value in the primary ranking column. In this blog, I want to explain step by step how ranking based on multiple columns (more than 2!) can help you solve this problem in DAX. The pattern used is something I picked up in this community, the focus in this blog is on the inner workings of that pattern.
Changing a Data Source connection in Power BI Desktop is very simple, just two or three clicks, as you can see in these two very simple methods:
Although it is a simple step to change the connection of a Data Source in Power BI Desktop, sometimes we face a problem that makes this change difficult, when the option button [Change Source ...] is disabled, which forces us to change manually within the Power Query Advanced Editor.
So, first we need to understand the logic that Power BI uses and why this option appears disabled. Everything happens within Power Query, as you can see, the first step we cannot exclude, precisely because Power BI understands that every first step of each query will be the Data Source. That is, if we start a query creating a variable instead of starting with the connection to the Data Source, the option to change the Data Source in the simple way you saw at the beginning of this article will be automatically disabled.
What can we do to fix this problem? We need to create these variables in a separate query, so we can use these variables in any other Query and at any step of it, but not in the first, as you can see in the image below. Also, it is a good practice of queries organization.
So, the first step of Query will always be the Data Source and you will always have the option button to change the Data Source activated, because Power BI will always find your first stage of Query your Data Source.
Our customers often ask us in our projects or workshops why Power BI is so successful in the area of Business Intelligence. Besides all the advantages like Self-Service BI (especially for non-technical persons), connectivity to a lot of data sources, creation of stunning reports and dashboards, great possibilities for sharing and collaboration etc. there is one big reason for it: DAX!
Multivalued column is a database design pattern where instead of normalizing and splitting data across multiple tables you keep multiple values in a single column. When used, this pattern requires extra modelling to work great in Power BI. This article explains how to do it!