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

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

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

 Calendar Table using Dax Syntax: CalendarTable = ADDCOLUMNS ( CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)), "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"), "Quarter", QUARTER([Date]), "DayOfWeek", WEEKDAY([Date]), "DayOfMonth", DAY([Date]), "WeekdayName", FORMAT([Date], "dddd"), "IsWeekend", IF( OR(WEEKDAY([Date]) = 1, WEEKDAY([Date]) = 7), TRUE(), FALSE()), "IsHoliday", IF( OR( MONTH([Date]) = 1 && DAY([Date]) = 1, MONTH([Date]) = 7 && DAY([Date]) = 4), TRUE(), FALSE()) ) Explanation: CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)) Creates a table with a single column containing dates from January 1, 2024, to December 31, 2024. ADDCOLUMNS ( Adds new columns to the table created by the CALENDAR function. "Year", YEAR([Date]), Adds a column named "Year" that...

 When to use Distinctcount and Countrows Function in DAX Distinctcount: Counts the number of distinct values in a column. Expression: Count_Of_EmpID =CALCULATE(     DISTINCTCOUNT('Table'[EmpID]),     ALLEXCEPT('Table', 'Table'[EmpID]) ) Explanation: This DAX expression calculates the count of distinct employee IDs in the 'Table' table, excluding any filters on other columns. The ALLEXCEPT function removes all filters from the 'Table' table except for the EmpID column, ensuring that the count is performed on all rows in the table. If we don't want to count EmpID whose having as 0 then follow below Expression Count_Of_EmpID=CALCULATE(     DISTINCTCOUNT('Table'[EmpID]),     ALLEXCEPT('Table', 'Table'[EmpID]),     'Table'[EmpID] <> 0 ) This expression adds an additional filter to the ALLEXCEPT function, excluding rows where the EmpID column is equal to 0. As a result, the count of distinct employee IDs will o...