Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# How To Compare Two Lists Of Calculated Data Virtually – An Advanced DAX Technique

by | 1:00 pm EDT | July 31, 2020 | Power BI

When working with calculated data, comparing different data sets will sometimes be necessary. I’m going to show you how to do that by combining different table functions. You may watch the full video of this tutorial at the bottom of this blog.

The INTERSECT function is the best thing to use in cases like these. Some may also say that EXCEPT is just as useful in comparing calculated data. But once you figure out how to use INTERSECT then it follows what you’ll be able to apply to the EXCEPT function in your calculations as well.

## Sample Data

To show what INTERSECT can do, I’m going to show you how to compare customers from two different time periods.

In the example below, I have a date slicer to help me zone in on the time frame I need. I also have a column for the states where customers place their orders. Of course, this could be a completely different dimension depending on the insights that you’re trying to get.

I also have my Total Revenue for each state, which is also shown in the visualization.

Now I want to see how many of the customers who made a purchase at a specific date last year also made a purchase on the same date this year. That means I’m looking at two specific lists of customers who made a purchase on the same date but in different years. From there, I’m going to find out how many customers appear on both lists.

## Comparing Customers From Two Time Periods

Before I can compare those two customer lists, I have to come up with the Total Customers first.

The Total Customers include those who made purchases at different stores in different states within the specified time frame. I used a simple formula to come up with this result.

I used the VALUES function because it brings up a single list of unique values. In this case, it’s going to show us the customers who bought products at the specified time frame.

I also used the COUNTROWS function. This will count the number of unique values from the list.

I’m referencing the Sales table for this measure.

Why not use the Customer table instead?

If I reference the Customer table directly, I’ll only end up counting every single customer regardless of the time frame. I need to make sure that the right context is used. By using the Sales table, I don’t only get the customers, I also get information where and when they made their purchase.

Now that I have the Total Customers, I can come up with the list of customers from the same date, but last year. This is where the measure becomes a bit more complicated.

Let me go into each part of this measure bit by bit.

Remember that I’m trying to change the context of the table. This is why I’m going to use the CALCULATETABLE function.

CALCULATE is a familiar function because I use it in my examples most of the time. It changes the context of a specific calculation. CALCULATETABLE, on the other hand, changes the context of an entire table.

Since I want to change the time frame, I’m going to use the DATESBETWEEN function.

Using CALCULATETABLE allows me to add filter parameters. By using the DATESBETWEEN function, I can zone in on a time frame that falls 365 days below the MIN and the MAX dates. In short, it pushes the time frame back to last year and creates another list of customers from there.

Again, I’m using the COUNTROWS function to calculate the number of customers after the additional filters have been applied.

Now that I have two lists, I can bring those measures I made into the INTERSECT function.

The INTERSECT function will virtually look into the list being referenced. In this case, that’s the Customer Name Index. It will go through every one of those customers and compare them with the second list from one year ago.

As it compares the calculated data, it looks at the ones that appear in both lists. For example, the table below shows that 224 customers made purchases on the same date on two different years in California.

## Conclusion

Hopefully, you can now imagine other scenarios where the INTERSECT function can be used to compare two sets of calculated data.

You can even present the comparison in percentages and apply some measure branching techniques. You can look at the customers who did not come back and send out some marketing material to them.

This really shows how much insight you can get once you get the hang of using different DAX functions in Power BI.

All the best,

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...