John Liu Blog

Every drop counts

Power BI DAX handle duplicate values

When you have detailed data mixed with summary data, you can’t directly using the simple aggregation function in PowerBI to do aggregation on the summary data as the results will not be correct (due to duplicate data). However, you can create a measure to lead PowerBI to do aggregation correctly. For example, you have following data: Project Name Allocated Hours User Name Used Hours Project1 40 User1 2 Project1 40 User2 4 Project1 40 User3 3 Project2 100 User1 20 Project2 100 User4 50 Project2 100 User3 10 When you need to report on Project Allocated Hours and Total Used Hours, the Allocated Hours can’t be simply sum up.

Posted by John Liu Friday, July 22, 2022

Power BI Date Table

There are a few ways to create date table in Power BI. Often, we need to create a few new columns to help with visual sorting etc. In Model View, click New Table and then using one of the following DAX functions to create the table and other computed columns as needed. Using DAX: Calendar = CALENDARAUTO() //returns a contiguous, complete range of dates that are automatically determined from your dataset Calendar = CALENDAR(date(2010,1,1),date(2050,12,31)) //returns a contiguous range of dates based on a start and end date that are entered as arguments in the function Calendar = CALENDAR(MIN('table'[date]),MAX('table'[date])) //similar to CALENDARAUTO() MonthNum = MONTH(Dates[Date]) WeekNum = WEEKNUM(Dates[Date]) DayoftheWeek = FORMAT(Dates[Date], "DDDD") FYMonthNum = VAR FYStartMonth = 7 //Update the fiscal year starting month above *Use number between 1 to 12 RETURN IF ( MONTH ( 'Calendar'[Date] ) >= FYStartMonth, MONTH ('Calendar'[Date] ) - ( FYStartMonth - 1 ), 12 + ( MONTH ('Calendar'[Date] )- ( FYStartMonth - 1 )) ) FYYear = VAR FYStartMonth = 7 //Update the fiscal year starting month above *Use number between 1 to 12 RETURN IF ( MONTH ( 'Calendar'[Date] ) >= FYStartMonth, YEAR ('Calendar'[Date] ) +1, YEAR('Calendar'[Date] ) ) Month = FORMAT([Date], "MMM") Year = YEAR('Calendar'[Date]) Using Power Query (MDX) to generate a list and then use Transform to Convert the list To Table and format the column as Date.

Posted by John Liu Thursday, March 24, 2022

FEATURED TAGS
ai api automation azure azure cosmos db azure openai azure vm base64 dax docker excel execution plan hyper-v infrastructure m machine learning machine learning services network network card performance power automate power bi power bi report tricks power query powershell python qgis query performance regex replication snowflake sql sql script sql server sql server admin sql server config ssl ssms troubleshooting unicode vmware