Weird behavior of power BI with TREATAS function

[KRISHP1234]

Summary:

When working with three logically related tables in Power BI, but where physical relationships cannot be established, and we must rely on the TREATAS function to calculate measures, we encounter a specific behavior:

• You cannot directly apply two TREATAS functions inside a single measure and expect the correct filter context.
• Instead, you must create a cover measure that handles the source-to-middle table relationship using TREATAS, then apply the final calculation through this measure.

Detailed Example with Quote Scenario:

Let's say we have 3 tables:

1. Quote
2. QuoteInvoice
3. Detail

Table Descriptions:

• Quote – Contains general quote information.
• QuoteInvoice – Contains multiple invoices associated with a single quote.
• Detail – Contains multiple invoice records based on multiple parts listed in each invoice.

Logical Relationships (One-to-many chain):

• Quote[QuoteId] → QuoteInvoice[QuoteId]
• QuoteInvoice[QuoteInvoiceId] → Detail[QuoteInvoiceId]

Note: There are no physical relationships; the model is logically correct only.

Initial (Incorrect) Attempt:

Trying to calculate revenue from the Detail table:

Total sales =  
    CALCULATE( 
        SUM(Detail[Revenue]), 
        TREATAS(VALUES(QuoteInvoice[QuoteInvoiceId]), Detail[QuoteInvoiceId]), 
        TREATAS(VALUES(Quote[QuoteId]), QuoteInvoice[QuoteId]) 
    )
  

Problem: This approach does not work correctly when plotting fields from the Quote table. Despite both TREATAS functions being used, the filter context does not propagate as expected.

Correct Approach with a Cover Measure:

To ensure proper context, create a cover measure for the relationship between Quote and QuoteInvoice and use it to drive the final calculation:

-- Base measure (Detail to QuoteInvoice)
Total sales =  
    CALCULATE( 
        SUM(Detail[Revenue]), 
        TREATAS(VALUES(QuoteInvoice[QuoteInvoiceId]), Detail[QuoteInvoiceId]) 
    )

-- Final measure (Quote to QuoteInvoice, then to Detail)
Total sales (final) =  
    CALCULATE( 
        [Total sales], 
        TREATAS(VALUES(Quote[QuoteId]), QuoteInvoice[QuoteId]) 
    )
  

Can anyone explain this behaviour?

[v-echaithra]

Hi @KRISHP1234 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @KRISHP1234 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @KRISHP1234 ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithra E.

[johnt75 · Accepted solution]

The 2 TREATAS functions are not being applied sequentially, they are being applied at the same time. What you want to happen is to get the values of quote invoice ID using the quote ID as a filter. You don't need to do this in a separate measure, you can use variables, e.g.

Total sales =  
VAR QuoteInvoices = CALCULATETABLE(
    VALUES(QuoteInvoice[QuoteInvoiceId]),
    TREATAS(VALUES(Quote[QuoteId]), QuoteInvoice[QuoteId]) 
)
VAR Result = 
    CALCULATE( 
        SUM(Detail[Revenue]), 
        TREATAS(QuoteInvoices, Detail[QuoteInvoiceId])
    )
RETURN Result