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

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

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

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

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