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 Embed Report in Application

This is a testing embed report with hidden filter: To hide the filters pan, clck on the eye icon aside the Filters header. The eye icon to hide individual filter doesn’t appear to be working in embed mode, until the entire filter pan is hidden. Following is the url with filter format URL?filter=Table/Field eq 'filter value' URL?filter=Table/Field in ('value1', 'value2') With ifram embeding, you will need to use &$ instead of ?

Posted by John Liu Thursday, September 21, 2023

Power BI Auto Select KPI using Field Parameter

(Note: at time of this post, both Field parameters and New card visual are still in preview. To use these two functions, go to File –> Options and settings –> Options –> Previw features, and turn on these features.) You might need to develop Power BI KPI report for different department/user with same visual format but different data. For example, a report with some card visual and line chart for different but targeting different KPI measures.

Posted by John Liu Sunday, September 17, 2023

Power BI Page Navigation

For page navigation button, we can’t set icon as with normal button. However, if we add the icon as part of the page name, the button will show on the navigation button. To add an icon to the page name, hold Windows logo key + period (.) key. This will open the emoji window for you to pick an emoji icon. Apart from the Window emoji, you may find icons from unicode-table or vertex42

Posted by John Liu Sunday, September 17, 2023