Forum Discussion
Filtering calculations by column in created table
I have 2 imported data tables - one for bonus data, one for overall data.
I have a created table (Year, Country) which pulls unique combinations for the country and year across 1 of the tables based on the calculation
GPG Calculations =
SUMMARIZE('Monthly Payroll Data','Monthly Payroll Data'[Year],'Monthly Payroll Data'[Country]).
e.g.
2022 Turkey
2023 Turkey
2022 France
I have created a new column with the measure
Mean Male Bonus =
Calculate(
Average('Bonus Data'[Total Bonus / Commission]),
'Bonus Data'[Year]='GPG Calculations'[Year],
'Bonus Data'[Country]='GPG Calculations'[Country],
'Bonus Data'[Gender]="Male",
'Bonus Data'[Total Bonus / Commission]<>Blank(),
'Bonus Data'[Total Bonus / Commission]<>0
)
I tried to calculate the average bonus value from the bonus table, filtering by the year and country (e.g. 2022 Turkey will calculate the average bonus when filtered by 2022 and Turkey).
I get an error - expression contains columns from multiple tables, but only columns from a single table can be used in a true/false expression.
I will need to use Monthly Pay Data later in other calculations, so I cannot change my filter expression.
Does anyone know what I can do to fix this?
1 Reply
- AnkitKukreja
Super User
Hi Anonymous
For using Calculate you would need to use the Filter function. This has been explained well by Marco and Alberto in the below link. I hope this helps you. If you are still facing this issue please share your model without PII info and I can look into it.
https://www.sqlbi.com/articles/solving-errors-in-calculate-filter-arguments/
