John Liu Blog

Every drop counts

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

Configure application auto start after reboot

To configure application auto run after current user login: First login in as the user Ctrl + R to start run, type in “sheel:startup” (which is folder like C:\Users\john\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup) Create a shortcut to the application required with appropriate parameter Note: you may need to redo the above each time following application reinstall/upgrade

Posted by John Liu Saturday, January 28, 2023

SQL Server Session Settings

Following SQL Server session settings impact on if an existing plan in cache can be used or not. Only if the session settings are identical to the settings for the cached plan, the plan can then be reused; otherwise, it will be recompiled. Different setting may impact performance at execution. ANSI_DEFAULTS ANSI_NULL_DFLT_ON ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL NUMERIC_ROUNDABORT For stored procedure, the ANSI_NULLS and QUOTED_IDENTIFIER settings will be used are the settings used when the stored procedure is created and runtime settings are irrelevant.

Posted by John Liu Tuesday, January 24, 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