Hi, I am new to DAX. I have an assignment which requires me to convert all 3 steps tables to only one table using CALCULATETABLE function. When i tried to do for the first 2 steps, there is an error shown as below.
Hereby I attach the 3 steps table for your reference:
forecasting_uoa_payroll_step1 = FILTER ( uoa_payslips_history_aggregated, DATEDIFF(uoa_payslips_history_aggregated[YearMonth],TODAY(),MONTH)<variable_uoa_per_cust_window[Variable_UoA_per_Cust_Window Value]+1 )
forecasting_uoa_payroll_step2 = SUMMARIZE ( forecasting_uoa_payroll_step1, forecasting_uoa_payroll_step1[Country], "UoA Per Customer Average", AVERAGE(forecasting_uoa_payroll_step1[UoA_per_Customer]) )
forecasting_uoa_payroll_step3 = SELECTCOLUMNS ( customer_payroll_forecast, "Date", customer_payroll_forecast[YearMonth], "Country", customer_payroll_forecast[Country], "Customers", customer_payroll_forecast[Total Headcount] )
I don't know if this issue was solved or not, but just to keep in mind for these kind of situations, we can not use both measures to check the boolean condition in the filter argument.
The following article explains the issue in detail.
If this helps 🙂
Thanks "mgb-nav-pbi", your linked article has solved my issue,
whereby I got the same error, when uing a measure in a CALCULATE expression as a filter,
e.g. measure < 155.
Just putting the FILTER function in front of the measure has solved my issue,
e.g. FILTER(measure < 155)
Rooms < $155 = CALCULATE(
'Invoice Line'[Item Group]="HOTEL",
FILTER ([measure] < 155)
This worked for me inside a calculate statment, anyone know the logic as to why this works? what is filter doing behind the scenes?
Hi @ilyan321 I'm finding it hard to understand what you're trying to acheive. To help with your wider goal it would be useful if you can post a picture of your model and provide some sample data in table format, or share the .pbix if you can.
A high-level description of what you're trying to do would help as well.
Some general comments on the code that may help you:
You could refactor step 2 as
forecasting_uoa_payroll_step2 = ADDCOLUMNS( VALUES(forecasting_uoa_payroll_step1[Country]), "UoA Per Customer Average", CALCULATE(AVERAGE(forecasting_uoa_payroll_step1[UoA_per_Customer])) )
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.