Getting Better at DAX: Understanding TREATAS() Function

This is a fun video in which we try to solve an interesting DAX puzzle while learning how to use TREATAS() DAX function. As a bonus I also show you how to debug variables that house a table inside. For that I do a bit of a tutorial on CANCATINATEX() function.

The data set is very simple, we only have two tables. The Sales table stores Date, Customer, Product and Sales columns to track our sales. The Prices table has a price information for every product. To make things interesting, we do not have a relationship between our two tables.

The puzzle is to figure out what was the highest price that each customer has every paid for the products that he/she has purchased.

Click on this link to download the model used in this video.

These are the DAX formulas used in the video:

Highest Price Paid =
var CurrentCustomer = Sales[Customer]
var ProductsPurchased =
CALCULATETABLE(
DISTINCT(Sales[Product]),
FILTER(
ALL(Sales),
Sales[Customer] = CurrentCustomer
)
)
return CALCULATE( MAX(Prices[Price]),
TREATAS(ProductsPurchased, Prices[Product])
)

Highest Price Paid (DEBUG) =
var CurrentCustomer = Sales[Customer]
var ProductsPurchased =
CALCULATETABLE(
DISTINCT(Sales[Product]),
FILTER(
ALL(Sales),
Sales[Customer] = CurrentCustomer
)
)
return CONCATENATEX(ProductsPurchased, [Product], “-“)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s