How to use filters from different tables in CALCULATE?

[hcr4]

I have an OrderLines tables with a Revenue column. I also have a Calendar table with FinancialYear, and a Route table with RouteNo (certain orders are delivered by certain routes). What I'm wanting to do is sum the Revenue for a specific Financial Year and also specific Route Nos. Here is what I've tried:

RevenueSum = CALCULATE(

    SUM(OrderLines[Revenue]),

    'Calendar'[FinancialYear] = 2021,

    'Route'['RouteNo] = 160 || 161 || 162

    )

Obviously CALCULATE cannot take two filters from different tables like this. What is the workaround? Alternatively, it might be easier for me to remove the FinancialYear filter and use a slicer for FinancialYear instead, but then I'd have to workout how to prevent CALCULATE from overriding the FinancialYear slicer. Any ideas?

Thank you.

[tamerj1 · Accepted solution]

Hi @hcr4 

It depends on your data model but in general it can and it should. Not sure if the formula that you shared is exactly as what you've been trying or is just a short syntax but the syntax this way is wrong. Please try

RevenueSum =
CALCULATE (
    SUM ( OrderLines[Revenue] ),
    'Calendar'[FinancialYear] = 2021,
    'Route'['RouteNo] IN { 160, 161, 162 }
)