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 M functions

Empty table This query returns a table with a single text column called YourColumnName and no rows. let Source = #table( type table [YourColumnName = text], {} ) in Source Conditional Formatting Highlight max and min This function returns the color value for the Total Sales figure based on if the value is the max/min or other. MaxMin = VAR Sales = [Total Sales] VAR MaxSalesOverall = MAXX( ALLSELECTED('dimDate'[Year],'dimDate'[Quarter],'dimDate'[QuarterNo]), CALCULATE([Total Sales]) ) VAR MinSalesOverall = MINX( ALLSELECTED('dimDate'[Year],'dimDate'[Quarter],'dimDate'[QuarterNo]), CALCULATE([Total Sales]) ) VAR Result = SWITCH( TRUE, Sales = MaxSalesOverall, "#22957e", Sales = MinSalesOverall, "#ff908c", "#7bc8fe" ) RETURN Result Above Average This function indicates if the Total Sales value is above average (1) or not (0).

Posted by John Liu Tuesday, August 6, 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