Value.Compare in Power BI, An Advanced Power BI Class Excerpt

This post is an excerpt from our Advanced Power BI class.

Importance of Data State

Analyzing data states in the data collected is generally the primary focus of our Power BI analyses. We look at aspects related to standards, compare dates to today’s date and execute other such comparisons. The business user who consumes your data is very focused on specific data states, which are defined and driven by their internal business rules. These rules will tend to change over time as the business evolves. Hence, it is important to implement your state definitions in a way that is flexible and reduces the number of changes necessary to implement a changed business rule.

Introducing Power BI Value.Compare

You’ll learn a technique using Power BI M Value.Compare, which enables you to easily convert dynamic ranges into states while reducing your data model maintenance effort. The need for this is that states, such as those of overdue invoices or tasks, where a large number of variances is returned, can create challenges which result in pieces of business logic being implemented in several different locations, like visual filters, etc.

Value.Compare enables you to easily convert the large number of potential values into a discrete set of states. This technique encapsulates the business logic into one place, reducing long term maintenance effort and places to maintain the business logic when the business rules inevitably change over time.

You’ll see two examples of Value.Compare usage. One example will show you how to use the function with comparing appointment dates to today’s date and converting variances to a state. We’ll show you how to use embedded data type conversions to prepare the data so that you can use Value.Compare. The other example will show how to use Value.Compare to determine Service Level Agreement compliance, based on duration values. This will show you an easy way to implement this logic and how to externalize the comparison value using a parameter.