Power BI Tips & Tricks

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

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