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