Posts

Solving the issue of "The UseRelationship() and CrossFilter() function may not be used when querying 'Table' because it is constrained by row-level security."

 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
Post a Comment
Read more

Making Dates Pop in Power BI: Adding Day Suffixes

Image
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: Getting the Day Number: We sta
Post a Comment
Read more

Sorting Day/Month/Year Column in Power BI

 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 sorting value
Post a Comment
Read more

Dynamically Changing Text Fields in Power BI Using DAX

  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
Post a Comment
Read more

Power BI Realtime scenarios

  Power BI Realtime scenarios Problem1: You have two tables in Power BI: Table1 and Table2. Table1 contains data with columns IB (Identifier), Date, and EmpID (Employee ID). Table2 contains data with columns EmpID and Date. You want to bring the IB values from Table1 into Table2, matching them based on EmpID and Date. However, you cannot create a direct relationship between the tables, and Table2 contains future dates. You need to copy the IB values to future dates in Table2 based on the latest available data up to each date. Solution: To achieve this, you can create a calculated column in Table2 using Data Analysis Expressions (DAX) in Power BI. This calculated column will look up the latest available IB value for each EmpID and date in Table2, considering only the dates up to and including the current date. Sample Data: Table1 IB Date EmpID SA123 3/1/2023 1 SA231 3/1/2023 1 SA123 4/1/2023 1 SA231 5/1/2023 1 SA345 6/1/2023 1 Table2 EmpID Date 1
Post a Comment
Read more

Dax calculation Filling Null based on ID column

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('YourTableName'[Name]),     CALCULATE(         FIRSTNONBLANK('YourTableName'[Name], 1),         FILTER(             'YourTabl
Post a Comment
Read more

Extracting English Names from Other Language Text Using DAX in Power BI

 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])     ),     [Value] ) RETURN MID(TextToSearch, NameStart, NameEnd - NameStart + 1) Explanation: VAR TextToSearch = 'YourTableName'[YourColumnName]: This line creates a variable named TextToSearch and assigns the value of the column 'YourTableName'[YourColum
Post a Comment
Read more