Power BI Tips & Tricks

 Extracting English Names from Other Language Text Using DAX in Power BI Dax Formula: EnglishName = VAR TextToSearch = 'YourTableName'[YourColumnName] VAR EnglishChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" VAR NameStart = MINX(     FILTER(         ADDCOLUMNS(             GENERATESERIES(1, LEN(TextToSearch), 1),             "Character", MID(TextToSearch, [Value], 1)         ),         CONTAINSSTRING(EnglishChars, [Character])     ),     [Value] ) VAR NameEnd = MAXX(     FILTER(         ADDCOLUMNS(             GENERATESERIES(1, LEN(TextToSearch), 1),             "Character", MID(TextToSearch, [Value], 1)         ),         CONTAINSSTRING(EnglishChars, [Character])     ),   ...

 Calculate Sales last day of each month Problem Scenario: Monthly Portfolio Reporting Background: You work as a financial analyst at a large investment firm. Your team is responsible for analyzing the performance of various investment portfolios on a monthly basis. Each portfolio consists of a diverse range of assets including stocks, bonds, and mutual funds. Problem Statement: Your team needs to generate monthly reports summarizing the performance of each investment portfolio. The reports should include key metrics such as total portfolio value, asset allocation, and performance returns. However, the challenge lies in identifying the latest available data for each month to ensure accurate reporting. Dax Function: Lastdateineachmonth = CALCULATE( MAX('Sales'[Date]), ALLEXCEPT('Sales', 'Sales'[Month]) ) Dax Explanation: CALCULATE Function: CALCULATE is a powerful DAX function used for modifying the context in which other functions are ev...

 Last 12 Months from Today Dax Formula: Last12Months =  IF (     AND (         'Calendar Table'[Date] >= EDATE(TODAY(), -11),         'Calendar Table'[Date] <= TODAY()     ),     1,     0 ) Explanation: This function appears to be designed to create a flag or indicator for dates falling within the last 12 months relative to the current date. Here's a breakdown of how the function works: 1) IF Statement: The function starts with an IF statement, which evaluates a condition and returns one value if the condition is true, and another value if the condition is false. 2) AND Function: Within the IF statement, there's an AND function, which checks if all of its arguments are true. In this case, it's checking two conditions. 3) EDATE Function: The EDATE function is used to calculate a date that is a specified number of months before or after another date. EDATE(TODAY(), -11) calculates the date that...

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...