The latest DAX functions in Power BI can be very "power"ful! Patrick starts by looking at the INDEX function to solve a customer problem! Blog announcement: https://powerbi.microsoft.com/blog/power-bi-december-2022-feature-summary/#post-21693-_Toc121395731 INDEX function (DAX) https://learn.microsoft.com/dax/index-function-dax WINDOW function (DAX) https://learn.microsoft.com/dax/window-function-dax Chris Webb's Blog https://blog.crossjoin.co.uk/2023/01/02/why-dax-window-functions-are-important-for-performance-in-power-bi-directquery-mode/ Jeffrey Wang's Blogs on Window Function https://pbidax.wordpress.com/2022/12/15/introducing-dax-window-functions-part-1/ https://pbidax.wordpress.com/2022/12/23/introducing-dax-window-functions-part-2/
About pleblancThis author has not yet filled in any details.
So far pleblanc has created 253 blog entries.
Continuing our T-SQL journey, Patrick explores the use of the OVER and RANK functions in combination with common table expressions (CTE) in Azure SQL Database and Azure Synapse Analytics. RANK (Transact-SQL) https://learn.microsoft.com/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver16 SELECT - OVER Clause (Transact-SQL) https://learn.microsoft.com/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16 WITH common_table_expression (Transact-SQL) https://learn.microsoft.com/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16
Your detective journey starts with the Kusto Detective Agency for Azure Data Explorer. This is a really fun way to learn the Kusto Query Languate (KQL)! Patrick gets you started! And, it's FREE! Kusto Detective Agency: https://detective.kusto.io/
You've set up Power BI Incremental Refresh and there is a day that is missed because schedule refresh failed or some other reason. What happens? Do you lose that data? Patrick digs in!
Want to know more about a slowly changing dimension? What about the different types? Patrick goes through examples and ties in the surrogate key! Sample: https://github.com/guyinacube/demo-files/blob/master/video%20demos/Synapse/20230124%20-%20SCD%20Demo.sql Handling Type II Dimension with the MERGE Statement https://sqldownsouth.blogspot.com/2010/11/handling-type-ii-dimension-with-merge.html Slowly Changing Dimension https://en.wikipedia.org/wiki/Slowly_changing_dimension Star Schema The Complete Reference https://amzn.to/3QJjw2E
In a Power BI Paginated Report, you can allow your report consumers to customize what they see in the report such as which columns they want in their table or matrix. Patrick shows you how! Sample RDLs: https://github.com/guyinacube/demo-files/blob/master/video%20demos/RDL/20230118%20-%20Paginated%20Show%20Hide%20Columns.zip
We've had some questions about what a Common Table Expression (CTE) is. Patrick describes what they are and how you can use them in SQL Server or Azure Synapse Analytics. WITH common_table_expression (Transact-SQL) https://learn.microsoft.com/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16 Sample Scripts - https://github.com/guyinacube/demo-files/tree/master/video%20demos/Synapse
Struggling to get dates when using a between date slicer, or a before, or after? Patrick walks through a straightforward way to get the date values when using a date slicer in Power BI!
Using Aggregation Tables in Power BI? Average calculations not working because they aren't hitting the agg? Patrick explains why this is happening and how you can correct it to get the expected result.
We've talked about using a surrogate key in your data warehouse whether that's Azure Synapse Analytics or something else. Patrick looks at why you should consider this even if you aren't using a slowly changing dimension.