Treatas is a powerful DAX function that applies the result of a table expression as filters to columns from an unrelated table.

In other words, treatas applies the filter context of a specific table to another unrelated table (no relationship in the model).

Book to Bill ratio

A book-to-bill ratio is the ratio of orders received vs orders shipped and billed for a specific period, a month in our case.

The book-to-bill ratio reveals how quickly a business fulfils the demand for its products.

How do we do this in DAX?

There’s a couple of ways to achieve it:

• Bidirectional filter + changing the filter context: bad practice will literally kill the performance of your model
• Intersect + Lookup: was ok before the treatas function but turns to be much slower than treatas
• Role playing dimension + userelationship function: by far the fastest but you need to use role-playing dimensions
• Treatas: if like me you cannot use role-playing dimensions for whatever reasons this is the best option

OK here is my model:

• Fact Order
• Order date dimension
• Ship date dimension

There isn’t any relationship between my two dimensions and the granularity level of my fact table is the date (day).

We’ve received 2,457 orders between Jan 2019 and March 2019 and those 2,457 orders have been delivered between Jan 2019 and May 2018.

And here I filtered out only the orders received in January so among those orders only 369 have been shipped the same month which gives the following ratio: Ratio = Orders Received/Orders Shipped = 0.358

OK now remember there’s no relationship between my two date dimensions so how do we propagate the filter context of the Order Date to the Ship Date? This where Treatas comes in.

Propagate the filter context to unrelated table with Treatas

Orders Shipped:
Only the orders shipped the same month as the received order are counted. This is done by propagating the filter context of the Order Date to the Ship Date.

Orders SHipped (treatas) = 
calculate(
                    sum('Fact Orders'[nb Orders]),
                    TREATAS(VALUES('Order Date'[YearMonthnumber]),'Ship Date'[YearMonthnumber])                 
)

Now we can compute the ratio applying the same logic.
Ratio (Book to Bill) = Total Orders Received / Total Orders Shipped

book to bill ratio (treatas) = 

DIVIDE(
calculate(
                    sum('Fact Orders'[nb Orders]),
                    TREATAS(VALUES('Order Date'[YearMonthnumber]),'Ship Date'[YearMonthnumber])
                    
),calculate(sum('Fact Orders'[nb Orders]))
)

In my example, my model contains two unrelated Date tables.
When I apply a filter to Order Date[Year Month] “Jan2019” the same filter, “Jan2019” is applied to Shipped Date[Year Month] hence the two tables must use similar column types such as Date, Product, Location, Customer…

You can read more about treatas in the microsoft docs