Antriksh Sharma

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 ) formatString: General Date...

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[Category],...

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 ) => INDEX ( N,...