Another important keyword to learn when using DAX Studio is the COLUMN keyword. In simplest terms, the COLUMN keyword allows you to create and mimic a calculated column in DAX Studio.
The following examples are a few of the ways you can use COLUMN in your codes in DAX Studio.
Basic Example
For this example, the Products Table will be the focus of discussion.
Before using COLUMN, you need to input the DEFINE keyword. You then need to create a column that will segregate an entire table into two categories: Trendy and Boring products.
In this case, products which are Red, Blue, Pink, and Black are deemed Trendy; while those excluded in this list are Boring. Using EVALUATE allows you to view the Products table.
If you run the code, you’ll see that a new column, Product Type, has been added. It categorized the products to either Trendy or Boring as specified by the DAX code.
Other DAX Functions With COLUMN
You can also use the COLUMN keyword together with other functions.
By adding FILTER, you can set the Product Type column to only show the value you specified. In this case, only results with a Trendy Product Type were returned. Only 985 rows are shown.
You can also use CALCULATETABLE.
In contrast to the previous DAX code, this returns 1,532 rows.
This is a very useful feature as the newly created column only persists for the current DAX session. That is, it won’t affect your prior or succeeding sessions.
Once you’re comfortable with the code, you can then transfer it to your Tabular Model using a quick copy and paste.
Another function you can use is SUMMARIZECOLUMNS on the Products Table.
Using this function will give you the two values under the Product Type column. This is useful when you want to look at the statistics for specific categories.
You can further add two columns that return the Total Sales and Percentage of each Product Type.
So in this case, Boring products account for 67% while the Trendy products account for 32% of the entire Total Sales amount.
You can further add other columns in the Results Table depending on what statistics you want to retrieve.
In this case, Products Category and Customer Gender were added to the DAX code. In the Results, you can see these as additional columns. The Total Sales and Percentage also change in value.
DAX Keyword: MEASURE And COLUMN
Another combination you can do is by using the MEASURE and COLUMN keywords together in a single query.
Create a measure on the Products Table that counts the number of rows. You can do this by using the COUNTROWS function. Then, use the ALLEXCEPT function to remove everything in the Products Table except for the Product Type column. Create another column, # Products, which will show the number of Products for the specified category.
If you try to run this code using EVALUATE, you’ll get a crowded Results Table. So trim it down by using SELECTCOLUMN.
Now, if you run this code, you’ll get a table comprised of four columns: the Product Key, Brand, Color, and # Products.
You can see two values, 985 and 1,532, in the # Products column. The number 985 corresponds to the number of products that are Trendy, and 1,532 for those in the Boring category.
Conclusion
The COLUMN keyword can easily be mixed and matched with other keywords and functions in DAX Studio. It’s a versatile keyword that allows you to add more columns or calculated columns to your existing table without affecting previous or succeeding DAX Studio sessions.
It’s important to understand how and when keywords can be used. This is fundamental knowledge when coding in DAX Studio.
Enterprise DNA Experts
***** Related Links *****
Power Query: How To Merge Tables W/ Different Columns
Add, Remove, & Rename Columns In R Using dplyr
Power Query Functions, Keywords, And Identifiers
***** Related Course Modules *****
Fundamentals In Power Query And M
DAX Studio For Power BI And SSAS – Beginner To Advanced
Tabular Editor 3: Beginner To Advanced
***** Related Support Forum Posts *****
Is It Possible To Summarize The Columns Of A Virtual Table In DAX?
DAX Studio – Table Size Returning Unexpected Results
Reordering Column Problem
For more DAX Studio Column queries to review see here….
