Using TREATAS DAX function in calculated columns

Great question! You’ve observed correctly: while TREATAS works as expected in a calculated measure, it does not function in calculated columns—even in Import mode. This isn’t just a DirectQuery limitation; it’s due to how calculated columns are evaluated in the Power BI engine.

Why does this happen?

• Calculated columns are evaluated row-by-row at data refresh time, not at query time. Functions like TREATAS, which conceptually “inject” filter context between tables, require the row context to be transformed into a filter context—a transformation that only happens in measures or calculated tables, not in calculated columns.
• In other words, TREATAS and similar “context transition” functions are designed for dynamic evaluation (measures, visuals), not for static column creation.

Alternative Approaches:

1. Use Calculated Table Instead:
   If you need to relate tables via TREATAS, consider creating a calculated table that combines or relates your data as needed.

2. Rework Logic for Merge/LOOKUPVALUE:
   For calculated columns, use LOOKUPVALUE, RELATED, or a combination of basic DAX functions that work row-by-row. For example, if you’re trying to fetch a value from another table based on a key, LOOKUPVALUE is usually the go-to alternative in calculated columns.

3. Push Calculation to Measures:
   If possible, move your logic to a measure. Measures can leverage TREATAS and are evaluated in the dynamic filter context of reports and visuals.

Sample for Calculated Column Alternative: If you’re trying to map a key from one table to another, you might use:

= LOOKUPVALUE(
    'No Call Required Campaigns'[SomeColumn],
    'No Call Required Campaigns'[Merchant_Data_Key], 
    'First Dial Data'[First_Dial_Data_Key]
)

Replace "SomeColumn" with the actual column you need.

Summary:
TREATAS is not supported in calculated columns because of how row context and filter context work in DAX. For row-by-row logic, stick to LOOKUPVALUE, RELATED, etc. For cross-table filtering logic, use measures or calculated tables.

Hope this clears things up!

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI