Using TREATAS DAX function in calculated columns

[nknareshkumar]

Hi Team,

I’m experimenting with the TREATAS function in DAX and noticed an inconsistency in its behavior. The function works as expected when used in a calculated measure, but it doesn’t work when applied in a calculated column.

Here’s what I’ve checked so far:

• According to Microsoft’s documentation, TREATAS is not supported in DirectQuery mode for calculated columns or row-level security (RLS) rules.

• However, my model is in Import mode, not DirectQuery, so this restriction shouldn’t apply.

Has anyone encountered this before? Any insights into why this might be happening or alternative approaches to achieve the same result in a calculated column?

Thanks in advance for your help!

treatas =
CALCULATE (
MIN ( 'First Dial Data'[Call Date] ),
TREATAS (
SUMMARIZECOLUMNS ( 'No Call Required Campaigns'[Merchant_Data_Key] ),
'First Dial Data'[First_Dial_Data_Key]
)
)

[johnt75]

There is no fundamental different between using TREATAS in a column or measure, at least in import mode, as CALCULATE will perform context transition. The different will be in the values that you are passing to TREATAS and the way they are calculated.

Which table are you trying to add the column to? 

What, if any, relationships does that column have to the rest of the model?

What value, or values, are you trying to pass to TREATAS ?

[burakkaragoz · Accepted solution]

Hi @nknareshkumar ,

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:

DAX

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

[nknareshkumar]

Thank you burakkaragoz  🙂