How to use filters from different tables in CALCULATE?

hcr4 — Fri, 16 Sep 2022 10:31:22 GMT

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.

Re: How to use filters from different tables in CALCULATE?

tamerj1 — Fri, 16 Sep 2022 10:39:41 GMT

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 } )

topic Re: How to use filters from different tables in CALCULATE? in DAX Commands and Tips

How to use filters from different tables in CALCULATE?

Re: How to use filters from different tables in CALCULATE?