Power BI Tips & Tricks

Exploring the CONTAINSSTRING Function in DAX Understanding the CONTAINSSTRING Function At its core, the CONTAINSSTRING function enables users to determine whether a specific substring exists within a given text string. This function returns a Boolean value, TRUE if the substring is found, and FALSE if it is not. Here's the basic syntax of the CONTAINSSTRING function: CONTAINSSTRING(<text>, <substring>) <text>: The text string within which you want to search. <substring>: The substring you're searching for within the text. Practical Example: Using CONTAINSSTRING in DAX Let's delve into a practical example to grasp how the CONTAINSSTRING function works within the context of DAX. Suppose we have a dataset containing product information, including a column named ProductName. We're interested in identifying which product names contain the word "apple". We can achieve this by creating a calculated column using the CONTAINSSTRING functi...

Understanding DAX Week Number Calculation with Conditional Logic Dax:  Week_num =     IF(         'Date'[WeekDayName] in {"Sat", "Sun"},         BLANK(),         WEEKNUM('Date'[Date], 2) -         WEEKNUM(EOMONTH('Date'[Date], -1) + 1, 2) + 1     ) Explanation: In Power BI and other Microsoft data analysis tools, understanding date calculations is crucial for accurate reporting and visualization. One common requirement is to calculate week numbers, but often, we need to exclude weekends from the count. The provided DAX function accomplishes this task efficiently. Introduction: Date-based analysis often involves tracking data by week, but the definition of a week may vary depending on business requirements. This DAX function not only computes week numbers but also intelligently handles weekends, ensuring that week counts accurately reflect working days. Function Overview: The  We...

Time Intelligence Dax Functions - Power BI TOTALYTD: Calculates the year-to-date total for a given expression. Example:  Total Sales YTD = TOTALYTD(SUM('Sales'[Amount]), 'Date'[Date]) TOTALQTD: Calculates the quarter-to-date total for a given expression. Example:  Total Sales QTD = TOTALQTD(SUM('Sales'[Amount]), 'Date'[Date]) TOTALMTD: Calculates the month-to-date total for a given expression. Example:  Total Sales MTD = TOTALMTD(SUM('Sales'[Amount]), 'Date'[Date]) DATESYTD: Returns a table that contains all dates from the beginning of the year up to the specified date. Example: Sales YTD = CALCULATE(SUM('Sales'[Amount]), DATESYTD('Date'[Date])) DATESQTD: Returns a table that contains all dates from the beginning of the quarter up to the specified date. Example: Sales QTD = CALCULATE(SUM('Sales'[Amount]), DATESQTD('Date'[Date])) DATESMTD: Returns a table that contains all dates from the beginning of the mon...