Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# New DAX Function COLUMNSTATISTICS â€“ Overview

This tutorial will walk you through COLUMNSTATISTICS() – a new DAX function, which is almost entirely undocumented. It has been added to Power BI in August 2021. You can watch the full video of this tutorial at the bottom of this blog.

This function can be used to quickly create metadata on every table in your data model, including a list of fields, min, max, max length, and a number of unique values (cardinality) for each field. However, there are some very unique aspects to how this function can and can’t be used, which I also cover in this blog.

I’ve been experimenting with this new DAX function and I want to show you what I’ve found and what I’ve been doing with it.

We’ll set up a test database using the Enterprise DNA Practice Dataset tool. So, we’ll turn that on and create a small three-year dataset with a standard sales-products-customers star schema model.

Now, let’s take a look at how this function works and, in some cases, how it doesn’t.

## Using The New DAX Function COLUMNSTATISTICS

First, Iâ€™ll create a new table to show what this function produces. So, let’s create a calculated table and we’ll call it Col Stats. You’ll notice that as I type in, IntelliSense doesn’t recognize it, which is a little bit of a red flag right off the bat. We also get this message that says a circular dependency was detected.

If you think about what this function is doing, itâ€™s going through each of the tables in the model and creating statistics on the fields. And so, what it’s doing here is it’s actually trying to create statistics on this table itself, the Col Stats table. It’s creating that circular dependency.

That’s not going to work. So my next thought was, instead of creating this as a physically calculated table, what if we created it as a calculated virtual table so that it’s not looping back on itself in a circular dependency?

So let’s go to measures, create a new measure, and just try something simple here. Weâ€™ll try counting the rows of this Col Stats table.

Now let’s try putting that into a card visual. As you can see, it works. We’ve got 71 rows in that column statistics stable. It’s calculating properly in that virtual environment.

Next, let’s take a look at how we can extract that data. If we can create a table, maybe we can go and do it as a DAX query.

For this, we go into the DAX Studio because we’re going to want to output this to a file and DAX Studio does a really nice job of that. We set our output here to Excel and we’ll create a static Excel table. Then, we do a DAX query. DAX queries that produce tables start with EVALUATE. It actually works and returns an Excel file with 71 rows.

Going back into Power BI in our sample data model, weâ€™ll take that Excel table that we created. Let’s pull it in, and we see we get this Query1, and it’s exactly what it says it was going to do, which is to produce information about each column in each table.

We can then transform this data. First, we just go Use First Row as Headers. Then, we take that Query1 table and rename it to Column Statistics. Then, click Close & Apply.

On a new page, we then take the table name and drag that into a slicer. Letâ€™s take the rest of the data and pull that into a table. And so now, we’ve got something that is quite useful. So instead of having to go back into the table view and scan for the fields you want, you can just click through in the course of your development. You can see all the summary statistics for all the columns.

## Conclusion

One of the things that are super cool about this is that because this is all in a physical table, in your DAX measures, you can now reference this. You can count your tables and the number of fields per table. In virtual tables, you can create lists of column names and do all sorts of checks against the structure of your model.

So, I think this is something that if we continue to experiment with, we’re going to find new and better uses for. If you’ve got some other discoveries you’ve made with regard to COLUMNSTATISTICS, I’d love to hear them in the comments.

You can watch the full video tutorial below for more details.

All the best!

Brian

## 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...

## Power BI Copilot: Enhancing Data Analysis with AI Integration

Are you ready to elevate your data analysis capabilities? Then let's delve into the realm of Power BI...

## What is Microsoft Fabric? A Guide to Features & Benefits

Imagine a one-stop shop that not only crunches numbers like it's eating cereal for breakfast but also...

## Python AttributeError: ‘dict’ object has no attribute Fix

Python is widely appreciated for its readability and ease of understanding. Among its numerous...

## Power BI vs Tableau: Differences Compared 2023

In the world of data visualization and analytics, two industry-leading data visualization tools stand...