New Options, Features, and DAX Functions

In Chapter 14, DirectQuery Connections to Power BI Datasets and Analysis Services in Composite Models, we learned how to use a DirectQuery connection to Power BI datasets, Azure Analysis Services, and SQL Server Analysis Services tabular models in a composite model. We also discussed some design challenges, especially when datasets include RLS. In this chapter, which is the book’s last chapter, we look into some newly released features, options, and DAX functions. This chapter covers the following topics:

• Fields parameters
• Introduction to Datamarts
• New DAX functions

Field parameters

Microsoft announced the field parameters feature in May 2022 for Power BI Desktop. At the time of writing, this feature is still in public preview. The field parameters feature helps Power BI developers to create data models that support more dynamic reports, where users can switch between different fields (measures and columns) in a slicer that affects other linked visuals. The field parameters feature is different from the query parameter and is accessible via the Report view, on the Modeling tab from the ribbon.

We already implemented a solution in Chapter 2, Data Analysis eXpressions and Data Modeling, under the Implementing Dynamic Measure Selection with Field Parameters subsection, to dynamically select between two measures, so we do not need to explain how to use field parameters again. Instead, we use the sample file to extend the solution and discuss what happens in the background in more detail. The sample file is accessible here: https://github.com...

Introduction to Power BI Datamarts

Microsoft announced the public preview availability of Datamarts in May 2022. When authoring this chapter, Datamarts are still in preview, so using them in production environments is not recommended. The Datamarts capability opens endless possibilities for organizations to achieve their data analytics goals on the Power BI platform. Before we look into the Power BI Datamarts, let us take a step back and understand the meaning of a Datamart.

What is a Datamart?

A quick search on the Internet provides multiple definitions by technology-leading companies such as IBM, Amazon, and Oracle. They all agree that a Datamart is a subject-oriented subset of a data warehouse focusing on a particular business unit, department, subject area, or business functionality. The Datamart’s data is usually stored in databases containing a moving frame required for data analysis, not the full history of data. For instance, organizations store the historical...

New DAX functions

As we all know, Microsoft constantly adds new features, options, and functions to Power BI. While writing this book, Microsoft introduced 11 new DAX functions. This section briefly covers the new functions that I personally find more interesting and have many use cases. This section does not explain the syntax of the new functions; instead, we will refer to Microsoft’s official documentation website and explain the trickier points.

This section uses the AdventureWorksDW2019 SQL Server database as our sample data source, which can be downloaded from the following URL: https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&WT.mc_id=DP-MVP-5003466.

NETWORKDAYS()

This function is very useful in calculating the number of working days by considering weekends and public holidays. We could calculate the number of working days before this function became available, but this function offers another level...

Summary

This chapter taught us about new data modeling options, features, and DAX functions. We looked closer at field parameters and how they become handy in making more dynamic reports. We also learned about Datamarts by implementing some simple samples. We connected to the underlying SQL endpoint from SSMS and looked closer at RLS implementation in Datamarts. Last but definitely not least, we implemented some scenarios using the new DAX functions.

This is the last chapter of this book; I hope you enjoyed reading it and that you learned some new techniques and ideas to deal with data modeling in Power BI. For more details about the topics discussed in this book—and future updates—keep an eye on my website: www.biinsight.com. I share the tech topics that I find interesting on social media; you can follow me on Twitter (https://twitter.com/_SoheilBakhshi) and LinkedIn (https://www.linkedin.com/in/bakhshi/).

Happy data modeling!

Join us on Discord!

Join...