Browsed by
Tag: dax

DAX measure Top N ranking with drill down

DAX measure Top N ranking with drill down

In this short post, we will explore how to create a DAX measure for Top N ranking with drill down. Purpose of this DAX Measure In many business scenarios, understanding the performance of products at different levels of a hierarchy (such as Category, SubCategory, and Product) is crucial. The ‘Top N Ranking with Drill Down’ measure in DAX allows users to dynamically rank products at each level of this hierarchy based on their sales performance. This dynamic capability means that…

Read More Read More

Multiple Linear Regression in Power BI

Multiple Linear Regression in Power BI

In this post, I will describe how to implement Multiple Linear Regression in Power BI using DAX only. In the February 2023 release, Power BI introduced a new function called “Linest“, so we will see how to use it to make predictions and interpret its result. Linear Regression Linear regression is a type of statistical analysis used to find the relationship between two variables. It is used to determine how one variable (dependent variable) is related to another variable (independent…

Read More Read More

Optimized median measure in Dax

Optimized median measure in Dax

In this post, I describe how to write an optimized median measure in Dax which under specific criteria can be 1,000 times faster than the built-in median function.Some time ago I was tasked to migrate a multidimensional cube to a tabular model, this cube had around 2 billion rows and I also had to create a median measure on the new tabular model. I first thought that the median would be much faster to calculate on a tabular model than on…

Read More Read More

Show text results from an R visual in Power BI

Show text results from an R visual in Power BI

In this short post, I will describe how to show text results from an R script visual in Power BI. Microsoft Idea Today there is already an idea submitted to Microsoft to enable this possibility however this idea does not have a lot of votes so it is not likely to be added anytime soon so then the workaround comes to the rescue! Why do we even need to show text result from an R visual? According to the multiple…

Read More Read More

Paired T-test in Power BI using DAX

Paired T-test in Power BI using DAX

In this post, I will describe how we can implement a paired t-test in Power BI using DAX only. What is T-Test A t-test is a type of inferential statistic that can be used to determine if the means of two groups of data are significantly different from each other. In other words, it tells us if the differences in means could have happened by chance. There are three types of t-test: An Independent Samples t-test compares the means for two groups. A Paired sample t-test compares means from…

Read More Read More

Univariate Statistics DAX Cheatsheet

Univariate Statistics DAX Cheatsheet

In this small post, I’m sharing and will keep up to date the list of the most common univariate statistics DAX functions available in Power BI, as of now most of them are built-in functions but the more complex ones still require writing some Dax code. I’m sharing this through a Power Bi app where we can simply copy/paste the DAX code of the selected function. The Power BI

Correlation Coefficient in Power BI using DAX

Correlation Coefficient in Power BI using DAX

In this post, I will describe what is the Pearson correlation coefficient and how to implement it in Power BI using DAX. What is the Correlation Coefficient The correlation coefficient is a statistical measure of the relationship between two variables; the values range between -1 and 1. A correlation of -1 shows a perfect negative correlation, and a correlation of 1 shows a perfect positive correlation. A correlation of 0.0 shows no linear relationship between the movement of the two variables. How…

Read More Read More

AB Testing with Power BI

AB Testing with Power BI

A/B testing, or split testing, is a digital marketing technique that involves comparing two versions of a web page or application to see which performs better.

In this post, I am going to share a step by step guide to implement AB Testing with Power BI.

SSAS/Power BI – Take Calculation Groups to the next level with Tabular Editor

SSAS/Power BI – Take Calculation Groups to the next level with Tabular Editor

Now it’s been a year since the game-changer new calculation groups feature has been released and I just thought that I still haven’t written a post on it. Goals of this Post Use Calculation Groups Use Tabular Editor Advanced Scripting Speed up SSAS/PowerBI model development Create reusbale script Setting up the Environment Power BI: Download here. Tabular Editor: Download here. SQL Server 2019 and later Analysis Services or Azure Analysis Services. At the time of writing this post, Calculation Group…

Read More Read More

Calculation Group in Power BI – Error: Time Calculation Requires selection of exactly one calculation item.

Calculation Group in Power BI – Error: Time Calculation Requires selection of exactly one calculation item.

I first thought that the recent calculation group feature wasn’t working in a matrix when selecting more than one calculation item from a slicer, until, I realised that all I had to do to fix it was to simply disable the “column subtotals” option. To be honest I find the error message shown by PBI misleading. So if you encounter the error”Time Calculation Requires selection of exactly one calculation item” here is what you have to do to fix it:…

Read More Read More

Dynamic format strings for currency conversion in PowerBi with DAX

Dynamic format strings for currency conversion in PowerBi with DAX

I’ve previously written an article on how to dynamically convert and change the currency of a measure using a slicer.Now let’s see how we can use dynamic format strings for currency conversion in PowerBi with DAX. Model Prerequisite Add the FormatString column into your currency dimension You can download the file with the sample format string from here.Make sure to use a Unicode type column to store the different format strings otherwise the collation of your DB might not support…

Read More Read More

DAX – TREATAS function – Book-to-Bill Ratio use case

DAX – TREATAS function – Book-to-Bill Ratio use case

Treatas is a powerful DAX function that applies the result of a table expression as filters to columns from an unrelated table. In other words, treatas applies the filter context of a specific table to another unrelated table (no relationship in the model). Book to Bill ratio A book-to-bill ratio is the ratio of orders received vs orders shipped and billed for a specific period, a month in our case. The book-to-bill ratio reveals how quickly a business fulfils the demand for…

Read More Read More

Visualising Deviation From Average in Power BI

Visualising Deviation From Average in Power BI

In this post, I illustrate how to implement Deviation From Average in Power BI and why we should use it. Margin is a key metric to assess high-level performance of a company. But sometimes we want to measure and compare specific shop or department or employee performance with the entire company overall performance and this where deviation from average metric comes to help. The requirement are as follows: Company overall margin over time (which is just the margin average) Employee…

Read More Read More