Power BI Tips & Tricks

 Solving the issue of "The UseRelationship() and CrossFilter() function may not be used when querying 'Table' because it is constrained by row-level security." Error Statement, Problem, and Solution: Error Statement: The error faced in the original scenario was related to the usage of USERELATIONSHIP function within row-level security constraints, leading to the error "The UseRelationship() and CrossFilter() function may not be used when querying 'Table' because it is constrained by row-level security." Problem: The error prevented the usage of USERELATIONSHIP within row-level security constraints, impacting the ability to establish relationships between tables for filtering purposes in certain contexts. Solution: To overcome the error and achieve the desired filtering based on dates without relying on USERELATIONSHIP, the DAX expression utilizes the TREATAS function along with ALL('Date'). This approach establishes a virtual relationship ...

Making Dates Pop in Power BI: Adding Day Suffixes Problem Statement: Have you ever wanted to display dates in your Power BI reports with a little extra flair? By default, Power BI offers some formatting options, but they can be limited. One common request is to add suffixes like "1st," "2nd," "3rd," or "4th" to the day of the month. Achieving this can be tricky, but fear not – we've got a solution! Dax Formula: DayFormatted =  VAR DayNumber = DAY(DimDate[CalendarDate]) VAR Suffix =     SWITCH(         TRUE(),         DayNumber = 1 || DayNumber = 21 || DayNumber = 31, "st",         DayNumber = 2 || DayNumber = 22, "nd",         DayNumber = 3 || DayNumber = 23, "rd",         "th"     ) RETURN     CONCATENATE(CONCATENATE(CONCATENATE(DayNumber, Suffix), " "), FORMAT(DimDate[CalendarDate], "mmm")) Result: Explanation: Here's how it works in simple terms: Gett...

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