John Liu Blog

Every drop counts

Power BI Dynamic Parameter in Direct Query

In Power BI, you may want to pass the filter value user selected as parameter to your direct query data source to SQL Server. You can achieve this using dynamic M parameter. Please note, Bind to parameter option will NOT be available in Power BI unless a Direct Query query presence. In Power Query Editor, create a M parameter using Manage Parameters. The data type for the parameter needs to match with the type of data you try to bind with.

Posted by John Liu Wednesday, August 7, 2024

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

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