Today, let's take a look at how to do Pivot and Unpivot in DAX. Ideally you would do this in SQL or Power Query but it never hurts to see how you could do same with DAX, infact you might get a better understanding of a very special DAX function. For Pivoting we are working with StudentsScore data. To PIVOT in SQL we could write following 2 queries that return different results depending on the requirement SELECT * FROM StudentScores AS S PIVOT ( SUM(Score) FOR Subject IN (Eng

Today, I am going to show you how to bring/join the rows of previous partition with the current partition in DAX, with the metrics for the previous rows in the current time frame. From the above image you can see there are 2 Customers in 2021 that are brought forward into 2022 with their Sales in 2021 and Sales in 2022. First order of business is to get TOPN Customers in each year and for that we can use TOPN paired with GENERATE or with WINDOW VAR N = 2 VAR TopCustomersEvery

In an earlier blog post I showed how to implement Fill Up and Fill Down in DAX that relied heavily on using Calculated Columns. However now that I have more experience with WINDOW functions I can show you a solution that does all of that with just measures. But first I will introduce a simpler dataset that makes it easier to consume. The table contains only 3 columns. These 3 columns together do not make up unique combination, the row (2025-02-01, null, null) is present twice

DAX has an interesting function named NONVISUAL, which marks a Filter in SUMMARIZECOLUMNS to impact only the grouping columns and not affect the measures. We have model where there are Years in the Dates[Year] from 2020 - 2026 but the Sales only occured for 2021 - 2025, so when a user writes a DAX Query like the following one it returns 5. DEFINE MEASURE Sales[#Years in Dates] = COUNTROWS ( ALLSELECTED ( Dates[Year] ) ) EVALUATE SUMMARIZECOLUMNS ( ProductCategory[C

In this blog let's take a look at how to use DAX's UDF in a DAX Query that mixes TABLE, COLUMN, MEASURE, and FUNCTION keywords. We start with a UDF that returns Dates[Year] by the position that we pass, the focus is primarily on First and Last values when there are Years in Dates table that do not have a row in Sales table. Following UDF code returns First and Last Years: DEFINE FUNCTION FxGetValueByPosition = ( N: INT64 VAL, OrderByCol: ANYREF EXPR, SortOrder: ANYREF EXPR )

Let's say you want Top 2 Colors of each Brand, how would you do that? Here are different ways of computing that. Using INDEX & UNION DEFINE VAR Temp = ADDCOLUMNS ( ALL ( Products[Brand], Products[Color] ), "@Sales", [Sales Amount] ) VAR Uno = INDEX ( 1, Temp, ORDERBY ( [@Sales], DESC ), PARTITIONBY ( Products[Brand] ) ) VAR Dos = INDEX ( 2, Temp, ORDERBY ( [@Sales], DESC ), PARTITIONBY ( Products[Brand] ) ) EVALUATE UNION ( Uno, D

Today, I am going to explain how to show values in a Power BI visual but only for the last month or last N months.

In a Matrix like above there are many places where you can show/hide values, when we think of it there are mainly 5 areas, Rows, Columns, Rows Total, Column Totals, and Grand Totals, but you can have many more levels by some boolean tricks. So how do we control where exactly to show a value or which area to hide them from? by using ISINSCOPE. This function not only tells whether you're grouping by a certain column at certain level but you can use it as boolean mask or an inte

In this blog let's take a look at how you can Fill blank cells with values before or after them also know as Fill Up or Fill Down in various tools.

DAX has 2 functions that allow you to JOIN tables and columns i.e. NATURALINNERJOIN & NATURALLEFTOUTERJOIN, although there is no...

In this blog let's take a look at why your DAX calculations are returning 100% instead of a fraction of the grand total. In DAX you can...

I have been thinking about this for a while, a series of blogs where I try to explain how a particular piece of DAX code works. For this...

DAX doesn't have the concept of global variables so reusing a piece of code by referencing it somewhere else isn't possible, luckily the...

While working with DAX, a common mistake that anyone makes (that I also made) is to think that applying a filter on a column of a...

One thing that we are used to is ALL functions ignore filters and return either a unique value or complete table when used as a table...

Recently I faced a situation where a simple measure such as COUNTROWS was returning a blank, yeah what's up with that? COUNTROWS...

DAX language of Power BI and SSAS Tabular doesn't have a function for Full Outer Join so in this blog we are going to see a work around...

Recently someone asked a question that whether they should store a column in a Tabular model in String or Integer format and in this short blog I just want to help you decide it by yourself. In Power Query I have create 2 tables with different data types Let's load these tables in the data model and open DAX Studio and view the vertipaq Analyzer metrics. We can see that both column have a Dictionary encoding: The size of the dictionary varies however, the overall size mention

Have you ever writen a piece of DAX Code that looks like this: and ended up confused how is it able to calculate how many years, days,...

SUM and SUMX are both aggregation functions in DAX. While SUM allows you to aggregate only one Physical column of a table, SUMX is more...

One of the most common time intelligence calculations that I see in each report is that of Previous Year, this calculation is pretty easy...

In this tutorial lets see how easy it is to create a running total in DAX either in SSAS, Power Pivot or Power BI. Running total is a...

One of the very common requirement of Power BI users is that of showing only those measures in a visual which are selected in a slicer....

Out of the box TE doesn't allows you to create Calculated Table in Power BI, but if you want you can easily do that. To be able to create...