Power BI Tips & Tricks

 Sorting Day/Month/Year Column in Power BI  Problem: When visualizing dates in Power BI or Excel, sorting them correctly can be a challenge, especially when you want to sort them by day within each month and across months. This becomes particularly tricky when filtering by year, as the sorting needs to consider both the day, month, and year components of the date. Solution: To address this sorting challenge, we can leverage a calculated column in Power BI or Excel that calculates a sorting value for each date. By assigning weights to the year, month, and day components of the date, we can ensure that the dates are sorted correctly in all scenarios. Step-by-Step Solution: Extract Day, Month, and Year Components: Extract the day component from the date. Extract the month component and multiply it by a significant weight. Extract the year component and multiply it by a weight if a specific year is selected. Calculate Sorting Value: Combine the weighted components to create a sort...

  Dynamically Changing Text Fields in Power BI Using DAX Introduction: In Power BI, dynamic reporting is crucial for providing users with insights that adapt to their selections and filters. One common requirement is to dynamically change text fields based on user selections, such as switching between "Year-to-Date" (YTD) and "Month-to-Date" (MTD) labels depending on the chosen time frame. In this blog post, we'll explore how to achieve this using DAX (Data Analysis Expressions) in Power BI. Solution Overview: We'll create a measure instead of a calculated column in our date table that determines whether it's YTD or MTD based on the user's selections. This approach ensures that the label dynamically updates as users interact with the report. Step-by-Step Guide: 1. Create a Measure: Begin by navigating to your date table in Power BI and creating a new measure. This measure will hold the dynamic label for YTD and MTD. 2. Write the DAX Formula: In t...

Dax calculation Filling Null based on ID column  Real Time Problem: I have a table that has two main columns: Account ID and Customer Name. The Customer Name column contains a lot of blanks but the Account ID field is complete, so I was wondering if there's a way to get PowerBI to recognise that Account ID 1 is used against the Customer Name, and therefore to fill in any blanks as a result of this? I know this is really confusing, here's an example:  Name     AccountID John          1 John          1 Null          1 John          1 So is there a way to get PowerBI to recognise that EmpName = Account ID 1, therefore any rows that have the Account ID 1 should fill any blank cells in the Customer Name column with EmpName?   I want these null cells filled so I can use the Customer Name as a Slicer. Solution: Filled_Name =  IF(     ISBLANK('YourTabl...

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

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

Current Month or Current Year or Current Day as a Default Selection To allow users to always select the current month, year, or day, follow the instructions Dax Example CurrentMonth = IF( 'Calendar Table'[Month] = FORMAT(TODAY(), "mmm"), "CurrentMonth", 'Calendar Table'[Month] ) For the year, simply replace the "Month" column with the "Year" column, and similarly for the day or week, follow the same process. Feel free to ask any questions about Power BI. I am here to help you. Happy Learning! 😊