Here is how to insert blank rows after each group, sample data: OrderID OrderDate Product Category Quantity NetPrice 1001 2025-01-01 Laptop Electronics 1 60000 1002 2025-01-02 Smartphone Electronics 2 42000 1003 2025-01-03 Headphones Accessories 3 4200 1004 2025-01-04 Laptop Electronics 1 65000 1005 2025-01-05 Smartphone Electronics 1 21000 1012 2025-01-12 Headphones Accessories 4 5500 With Excel's Dynamic Array Functions: =LET( Table, Sales, u, UNIQUE(Sales[Product]), n, 1,

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

In Power BI if you create a relationship between a Date and DateTime column then you will see that the relationship doesn't work or if it works then it only works for time that is 12:00AM / Midnight. You can overcome this limitation by changing the Join On Date Behavior property in Tabular Editor. First I have created a DateTime version of OrderDate in Sales table createOrReplace ref table Sales column OrderDateTime = Sales[OrderDate] + ROUND ( RAND(), 2 ) formatStri

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’m going to show you an application I built with the help of ChatGPT that generates large volumes of Sales data based on the Microsoft's ContosoRetailDW model.

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