More than Excel BI
There are multiple ways of averaging values in DAX. Some of the most popular ways are mean (AVERAGE), median (MEDIAN) and mode (no built-in function; see DAX Patterns for an example). Yet another is TRIMMEAN, which exists in Excel. There is no corresponding function in DAX, and this blog post shows how you can replicate the function in DAX. Continue reading “TRIMMEAN in DAX”
August 2019 saw the introduction of two new DAX functions: CONVERT and REMOVEFILTERS. They are so new that at the time of writing this blog post (23 August 2019), they are only available in Azure Analysis Services and Power BI service, and even DAX Guide does not list them. In this blog post I’m showing what they are doing. Continue reading “New DAX functions: CONVERT and REMOVEFILTERS”
The Sort by Column feature of Power BI and tabular data models in general is well-known. The simplest example is sorting month names by their actual position in a year instead of the default (alphabetic) order. There is a popular misconception that there must be a one-to-one mapping between the column you are sorting and the column you are sorting by. This is not so, and in this blog post I am discussing unusual applications of the Sort by Column feature. Continue reading “Sort by Column in Power BI”
The latest version of DAX Studio, 2.8.0, was released yesterday. It has several new features, one of which stands out for me: Export All Data. While you could write your queries to CSV files or Excel before, this feature allows you to export the whole data model at once to CSV files or — and this is why I really like it — SQL Server. In this blog post, I am showing how to do it. Continue reading “Exporting all data from a Power BI data model”
A new DAX function is coming in the November 2018 release of Power BI Desktop: ISINSCOPE. You can already try the new function in Power BI service and Azure Analysis Services. In this blog post, I am going to show where ISINSCOPE can be useful. Continue reading “New DAX function: ISINSCOPE”
There are several DAX functions that deal with blanks. Surprisingly, some of them have a different idea of a blank. In this article, I am covering these discrepancies. Continue reading “Different blanks in DAX”
In August 2018, the Power BI team has released a new feature: conditional formatting by field value. This feature works for both background colors and font colors. Because different background colors require different font colors for text to be legible, it makes sense to use both at the same time. In this blog post, I am showing how you can dynamically choose the font color depending on the background color. Continue reading “Choosing font color based on background color in Power BI”
There are several undocumented DAX formula bar keyboard shortcuts in Power BI Desktop. For instance, you can comment or uncomment portions of your code, go to a specific line of code, and do other things. Some of the keyboard shortcuts are more useful than others — this blog post lists all that I am aware of. Continue reading “DAX formula bar keyboard shortcuts in Power BI Desktop”
Currently, Power BI does not have a built-in feature that enables you to switch between hierarchy levels with a slicer. While some field wells let you use a hierarchy, others, such as Legend, do not. In this article, I am showing a way to choose a hierarchy level dynamically. Continue reading “Dynamic hierarchies in Power BI”
There is a new DAX function coming soon to Power BI Desktop: COMBINEVALUES. It concatenates an unlimited number of values with a delimiter. You can already use the function by connecting to a dataset in Power BI service and creating report-level measures. Continue reading “New DAX function: COMBINEVALUES”