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”
Category: DAX
Choosing font color based on background color in Power BI
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”
DAX formula bar keyboard shortcuts in Power BI Desktop
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”
Dynamic hierarchies in Power BI
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”
New DAX function: COMBINEVALUES
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”
Configuring “all” access with dynamic row-level security
Row-level security is an important part of many data models. In this article, I am showing a way to selectively grant “all” access with dynamic row-level security and only one role. For example, we can let a CEO see the whole data model, but a department head will be able to see their department’s data only. This article presents a more flexible approach in cases where using an organizational hierarchy and PATHCONTAINS would be inappropriate. Continue reading “Configuring “all” access with dynamic row-level security”
Simple linear regression in DAX
DAX, originating in Power Pivot, shares many functions with Excel. As of 2017, some of the functions, such as SLOPE and INTERCEPT, exist in the latter but not in the former. The two functions can be used for a simple linear regression analysis, and in this article I am sharing patterns to easily replicate them in DAX. But first, why would you want to do such analysis? Continue reading “Simple linear regression in DAX”
Pearson correlation coefficient in DAX
If you had tried calculating the Pearson correlation coefficient (PCC) in DAX, you would have likely read Gerhard Brueckl’s excellent blog post. If you haven’t, I encourage you to read it, as it contains a high-level overview of what PCC is. In this article I am showing how the same calculation can be done more efficiently using the new DAX functions, which are available starting with Power Pivot in Excel 2016, Power BI Desktop, SQL Server 2016 Analysis Services, and Azure Analysis Services. Continue reading “Pearson correlation coefficient in DAX”