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

In this blog let's take a look at how to create cartesian product of 2 or more columns/lists in PowerQuery with UI, List Functions and Recursion when number of columns aren't fixed.

In this blog let's take a look at how to compute New and Returning Customers with Excel's Dynamic Array Functions and Power Query.