John Liu Blog

Every drop counts

Power BI Smart Axis

When developing Power BI report with date slicer, the large date range might make the visual overwhelming if the X axis interval is on a fix date/week/month/quater interval. It would be helpful if the X axis can auto adjust the interval betwee date/week/month/quater/year. Assumed you have a date table dimDate in your report. We can add another AutoPeriod table (in Model View –> click on New table) to define the possible intervals for the axis:

Posted by John Liu Sunday, September 17, 2023

Power BI Excel file

Excel can be used as data source for Power BI report. When the Excel file is on SharePoint or OneDrive, using SharePoint data source might have performance issue when it tries to navigate through the SharePoint folder/files. We can use Web data source to connect to the Excel file directly to eliminate the overhead with folder/file navigation. If you used the copy path option to get a direct link to the file from OneDrive/SharePoint you can use the URL as is.

Posted by John Liu Thursday, July 20, 2023

Power BI CALCULATE() examples

The CALCULATE() function is a powerful function used to manipulate the filter context. The first argument takes an expression or a measure (a measure is just a named expression). Subsequent arguments allow modifying the filter context. Total Sales for 2015 = CALCULATE( SUM('Sales OrderDetails'[Total Price]), YEAR('Sales OrderDetails'[orderdate]) = 2015 ) Sales by Ship Date = CALCULATE( Sum(Sales[TotalPrice]), USERELATIONSHIP(Sales[ShipDate],'Calendar'[Date]) ) Last Inventory Count = CALCULATE ( SUM ( 'Warehouse'[Inventory Count] ), LASTDATE ( 'Date'[Date] ) ) Sales All Region = CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS(Region) //The REMOVEFILTERS() function removes active filters.

Posted by John Liu Thursday, February 23, 2023

Power BI hierarchy

In Power BI, you can create new hierarchy in Model View. There are simply one-level hierarchy, such as category and subcategory for product. A more complex one is the multi-level parent-child hierarchy, such as orgnisation management chart which has multiple levels. For the complex parent-child hierarchy, Power BI does not flatten the hierarchy by default and this needs to be done manually using PATH() AND PATHITEM() functions. //create a new column, Path //The PATH() function returns a string contains a delimited list of IDs, starting with the top/root of a hierarchy and ending with the specified ID.

Posted by John Liu Thursday, February 23, 2023

Power BI notes

Number formatting “#,0.00” two decimal with thousand separator “#,0” no decimal with thousand separator “#,#” no decimal with thousand separator “P0” no decimal point “P2” 2 decimal points Workout the first day of the week based on sepecified day =DateAdd(DateInterval.Day ,1 ,DateAdd(DateInterval.Day ,DatePart(DateInterval.WeekDay ,iif(IsNothing(Parameters!ReportDate.Value),Today(),Parameters!ReportDate.Value) ,FirstDayOfWeek.Sunday ) * -1 ,iif(IsNothing(Parameters!ReportDate.Value),Today(),Parameters!ReportDate.Value) ) )

Posted by John Liu Friday, December 23, 2022