Filter & Sum over multiple Tables

[millercj]

I have a measure that calculates correctly:

KickerCalcClara = 
CALCULATE(
    SUM( 'PolicyTerms'[AnnualizedOriginalPremiumTRUE] ),
    FILTER ( ALL ( PolicyTransactions ) ,  'PolicyTransactions'[TransactionType]="NBS" && PolicyTransactions[EntryStamp]>=date(2025,1,3) && PolicyTransactions[EntryStamp]<=date(2025,4,1)
))

I need to add an additional filter based on a different table. I'm sure it's my syntax bit I'm not sure how to achieve that as I keep getting an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

The neccessary additional filter is:

'Producers'[DisplayName]="Clara"

Any help would be appreciated.

[lbendlin · Accepted solution]

KickerCalcClara = 
CALCULATE(
    SUM( 'PolicyTerms'[AnnualizedOriginalPremiumTRUE] ),
    'PolicyTransactions'[TransactionType]="NBS",
    'PolicyTransactions'[EntryStamp] IN CALENDAR("2025-01-03","20205-04-01"),
    'Producers'[DisplayName]="Clara"
)

[v-saisrao-msft]

Hi @millercj,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @millercj,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @millercj,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft · Accepted solution]

Hi @millercj,

Thank you for reaching out to the Microsoft Fabric Forum Community.

It seems you're trying to apply a filter on the Producers table while also calculating a sum with specific filters on the Policy Transactions table. The problem occurs because you are attempting to directly reference Producers [DisplayName] in a CALCULATE function without establishing the necessary context or relationship between the tables. Thank you @lbendlin, for your insights.

KickerCalcClara =  

CALCULATE( 

    SUM( 'PolicyTerms'[AnnualizedOriginalPremiumTRUE] ), 

    FILTER( 

        ALL('PolicyTransactions'), 

        'PolicyTransactions'[TransactionType] = "NBS" && 

        'PolicyTransactions'[EntryStamp] >= DATE(2025,1,3) && 

        'PolicyTransactions'[EntryStamp] <= DATE(2025,4,1) 

    ), 

    TREATAS( {"Clara"}, 'Producers'[DisplayName] )

)

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[lbendlin · Accepted solution]

KickerCalcClara = 
CALCULATE(
    SUM( 'PolicyTerms'[AnnualizedOriginalPremiumTRUE] ),
    'PolicyTransactions'[TransactionType]="NBS",
    'PolicyTransactions'[EntryStamp] IN CALENDAR("2025-01-03","20205-04-01"),
    'Producers'[DisplayName]="Clara"
)