Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Yrstruly2021
Helper V
Helper V

True/False expression that is used as a table filter expression

I am trying to add this code:

CALCULATE(
SUM(‘ZTBR’[Amount in USD]),
‘ZTBR’[Classification] IN {
“Below MOP”,
“Interest expense”,
“Profit before Brambles allocations Total”,
“Profit before Brambles allocations”,

 
		"Interco dividends",
		"Interest expense"
		
		}

To:

Totals by Account Description & Code3 =
VAR SelectedCashFlowDescription = SELECTEDVALUE(‘CashflowDiscriptions’[Description], “None”)
VAR _TotalNetDept = CALCULATE([Net debt increase/decrease], ALL(CashflowDiscriptions))
VAR _TotalNetTaxPaid = CALCULATE([Net Tax Paid], ALL(CashflowDiscriptions))
VAR _TotalIntercoCashFlows = CALCULATE([IntercoCashFlows], ALL(CashflowDiscriptions))
VAR _TotalOtherCashFlowItems = CALCULATE([Other cash flow items], ALL(CashflowDiscriptions))
VAR _TotalInterestPaid = CALCULATE([Interest paid], ALL(CashflowDiscriptions))
VAR _TotalInterestExpenseNotImpactingCashFlow = CALCULATE([Interest expense not impacting cash flow], ALL(CashflowDiscriptions))
VAR _TotalCashFlowFromOpsStatutory = CALCULATE([Cash flow from ops - statutory], ALL(CashflowDiscriptions))
VAR _TotalOtherCashFlowfromTradingAdjustments = CALCULATE([Other cash flow from trading adjustments], ALL(CashflowDiscriptions))
VAR _TotalDisposalsImpairmentofFixedAssets = CALCULATE([Disposals & impairment of fixed assets], ALL(CashflowDiscriptions))
VAR _TotalWorkingCapitalMvtsExclProvisions = CALCULATE([Working capital mvts excl. provisions], ALL(CashflowDiscriptions))
VAR _TotalOtherPPEAcquired = CALCULATE([Other PP&E acquired], ALL(CashflowDiscriptions))
VAR _TotalPurchasesOfPPE = CALCULATE([Purchases of PP&E], ALL(CashflowDiscriptions))
VAR _TotalWorkingCapitalMvtsInclProvisions = CALCULATE([Working capital mvts incl. provisions], ALL(CashflowDiscriptions))
VAR _TotalProceedsOtherPPEDisposals = CALCULATE([Proceeds other PP&E disposals], ALL(CashflowDiscriptions))
VAR _TotalProceedsPoolingDisposals = CALCULATE([Proceeds pooling disposals], ALL(CashflowDiscriptions))
VAR _TotalProceedsFromDisposals = CALCULATE([Proceeds from disposals], ALL(CashflowDiscriptions))
VAR _TotalPoolingEquipmentAcquired = CALCULATE([Pooling equipment acquired], ALL(CashflowDiscriptions))
VAR _TotalDisposalProceedsCashFlowAdjustments = CALCULATE([Disposal proceeds cash flow adjustments], ALL(CashflowDiscriptions))
VAR _TotalCapitalExpenditure = CALCULATE([Capital expenditure], ALL(CashflowDiscriptions))
VAR _TotalWorkingCapitalBudgetFlex = CALCULATE([Working capital mvts incl. provisions], ALL(CashflowDiscriptions))
VAR _TotalCashFlowfromTrading = CALCULATE([Cash flow from trading], ALL(CashflowDiscriptions))
VAR _TotalCashFlowFromOpsManagement = CALCULATE([Cash flow from ops - management], ALL(CashflowDiscriptions))
VAR _TotalCashFlowImbalance = CALCULATE([Cash flow imbalance], ALL(CashflowDiscriptions))
VAR _LeasesPrincipalRepaid = CALCULATE(SUM(Bracs[Metric Value ($)]), Bracs[Metric] = “Leases principal repaid”)

RETURN
SWITCH(
TRUE(),
SelectedCashFlowDescription = “Interco capital returned”, 678,
SelectedCashFlowDescription = “Net debt increase/(decrease)”, _TotalNetDept,
SelectedCashFlowDescription = “Cash flow imbalance”, _TotalCashFlowImbalance,
SelectedCashFlowDescription = “Cash flow from ops - management”,_TotalCashFlowFromOpsManagement,
SelectedCashFlowDescription = “Working capital mvts incl. provisions”, _TotalWorkingCapitalBudgetFlex,
SelectedCashFlowDescription = "Proceeds from disposals ", _TotalProceedsFromDisposals,
SelectedCashFlowDescription = “Proceeds other PP&E disposals”, _TotalProceedsOtherPPEDisposals,
SelectedCashFlowDescription = “Proceeds pooling disposals”, _TotalProceedsPoolingDisposals,
SelectedCashFlowDescription = “Capital expenditure”, _TotalCapitalExpenditure,
SelectedCashFlowDescription = “Net tax paid”, _TotalNetTaxPaid,
SelectedCashFlowDescription = “IntercoCashFlows”, _TotalIntercoCashFlows,
SelectedCashFlowDescription = “Leases principal repaid”, _LeasesPrincipalRepaid,
SelectedCashFlowDescription = “OtherCashFlowItems”, _TotalOtherCashFlowItems,
SelectedCashFlowDescription = “Interestpaid”, _TotalInterestPaid,
SelectedCashFlowDescription = “Interest expense not impacting cash flow”, _TotalInterestExpenseNotImpactingCashFlow,
SelectedCashFlowDescription = “CashFlowFromOps - Statutory”, _TotalCashFlowFromOpsStatutory,
SelectedCashFlowDescription = “Other cash flow from trading adjustments”, _TotalOtherCashFlowfromTradingAdjustments,
SelectedCashFlowDescription = “Disposals & impairment of fixed assets”, _TotalDisposalsImpairmentofFixedAssets,
SelectedCashFlowDescription = “Working capital mvts excl. provisions”, _TotalWorkingCapitalMvtsExclProvisions,
SelectedCashFlowDescription = “Working capital mvts incl. provisions”, _TotalWorkingCapitalMvtsInclProvisions,
SelectedCashFlowDescription = “Cash flow from trading”, _TotalCashFlowfromTrading,
SelectedCashFlowDescription = “Pooling equipment acquired”, _TotalPoolingEquipmentAcquired,
SelectedCashFlowDescription = “Other PP&E acquired”, _TotalOtherPPEAcquired,
SelectedCashFlowDescription = “Purchases of PP&E”, _TotalPurchasesOfPPE,
SelectedCashFlowDescription = “Working capital mvts incl. provisions”, _TotalWorkingCapitalBudgetFlex,
SelectedCashFlowDescription = “FX on foreign currency denominated debt”,
CALCULATE(
SUM(‘ZTBR’[Amount in USD]),
‘ZTBR’[Roll_Up_Function] = “FX on foreign currency denominated debt”
),
// Default calculation if no specific match is found
CALCULATE(
SUM(‘ZTBR’[Amount in USD]),
‘ZTBR’[Roll_Up_Function] IN {
“Cash share issues by Brambles Limited”,
“Cost sharing payments to Brambles Limited”,
“Cash paid for share buyback”,
“Equity raised cash flow adjustment”,
“Cash flow from ops - management”,
“Cash flow from trading”,
“Change in capex creditors”,
“Profit before Brambles allocations Total”,
“Depreciation and amortisation”,
“IPEP expense”,
“Disposals & impairment of fixed assets”,
“Profit on disposal of pooling equipment”,
“Scrapped pooling equipment”,
“Impairment or valuation adjustment of pooling equipment”,
“Disposals or valuation adjustments of other fixed assets”,
“Other cash flow from trading adjustments”,
“Other current debtors”,
“Share-based payments expense”,
“Working capital mvts excl. provisions”,
“Debtor movements”,
“Creditor movements”,
“Inventory movements”,
“Prepayment movements”,
“Change in loss compensation balances”,
“Change in capex creditors”,
“Provision movements”,
“Change in capex creditors”,
“FX on foreign currency denominated debt Total”,
“Brambles allocations not in mgt cash flow”,
“Interco interest and guarantee fees”,
“Interco cash flows”,
“Interco royalties”,
“Interco cash flow adjustments”,
“Internal restructuring”,
“Interco dividends Total”,
“Change in interco balances”,
“Change in interco recharge clearing”,
“FX on interco debt”,
“Interco capital returned”,
“Interco cash flow adjustments”,
“Interest expense Total”,
“Interest revenue”,
“Interest paid”,
“Interest received”,
“Cash flow from ops - statutory adjustment”,
“Lease interest”,
“Statutory reallocations”
}
) + CALCULATE(
SUM(Bracs[Metric Value ($)]),
Bracs[Metric] IN {
“Entity acquisitions and disposals”,
“Entity acquisitions”,
“Total purchase price of entities acquired”,
“Purchase price of entities acquired”,
“Cash price of entities acquired”,
“Non-cash consideration in purchase price”,
“Costs directly incurred on entity acquisitions”,
“Deferred settlement of entity acquisitions”,
“Deferred settlements now paid”,
“Non-cash consideration for entity acquisitions”,
“Net cash held by entity at date of acquisition”,
“Entity acquisition cash flow adjustments”,
“Proceeds from entity disposals”,
“Net price of entities disposed”,
“Agreed sale price of entities disposed”,
“Cash price of entities disposed”,
“Non-cash consideration in agreed sale price”,
“Costs directly incurred on entity disposals”,
“Provisions raised on entity disposals”,
“Net change disposal provisions and accruals”,
“Deferred settlement of entity disposals”,
“Deferred settlements now received”,
“Non-cash consideration for entity disposals”,
“Net cash held by entity at date of disposal”,
“Entity disposal cash flow tax adjustments”,
“Entity disposal cash flow other adjustments”,
“Pension plan adjustment”,
“Pension plan adjustment”,
“Working capital - budget flex”,
“Pooling equipment additions”,
“Pooling equipment replacements”,
“Pooling equipment internal transfers”,
“Other PP&E additions”,
“Other PP&E replacements”,
“Other PP&E internal transfers”,
“Joint venture loans”,
“WDV pooling equip. disposals & write-offs”,
“Gain pooling equip. disposals & write-offs”,
“WDV other PP&E disposals”,
“Profit other PP&E disposals”,
“Discount unwind on long term provisions”,
“Tax paid”,
“Joint venture loans”,
“Tax refunded”,
“Working capital - budget flex”,
“Fiscal unity tax transfers”,
“Other cash flow items”,
“FX adjustments to cash flow”,
“Change in cash net of overdraft”
}
)
)

Im geting the error below:

“A function ‘CALCULATE’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.”

 
3 REPLIES 3
v-weiyan1-msft
Community Support
Community Support

Hi @Yrstruly2021 ,

 

This error message means that you are trying to use a measure or another CALCULATE expression as a filter argument in the CALCULATE function. However, this is not allowed because CALCULATE only accepts simple True/False expressions or table expressions as filter arguments. You cannot use a scalar value or a context transition as a filter argument.
For more information and examples about solving errors in CALCULATE filter arguments, please refer to:

Solving errors in CALCULATE filter arguments - SQLBI

Hope that helps.
If the problem is not resolved, please feel free to contact us.


Best Regards,
Yulia Yan


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

talespin
Solution Sage
Solution Sage

hi @Yrstruly2021 ,

 

You have 24 CALCULATE statements in one measure, You might want to explore options to achieve what you are trying to do. To me it looks like you are calculating GrandTotals in variables and returning them + other stuff?

 

If you can explain what and how you are doing in your report, others can suggest better approach.

 

 

lbendlin
Super User
Super User

where and how are you trying to add that part?

 

Note that the SWITCH statement also allows you to specify values directly. That comes handy when you don't need to perform logic calculations and are only picking between values.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.