Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
Dear Power BI Experts,
i have problems writing this dax formula. I want to group one table (FCT_CONTRACT_EMPLOYEE, first picture) by employee_id to get the average daily rate of an employee. This should be multiplied with planned_working_days in another table (FCT_TIME_PLANNING_EMPLOYEE_LEGACY, second picture) based on the lookup column employee_id.
Right now it looks like this. The error says i cant use "EmployeeAvgDailyRate" because a base table is expected instead, but i thought that SUMMARIZE function works like that:
Solved! Go to Solution.
You can't use LOOKUPVALUE with a variable, it has to be a base table. Try
Hochrechnung Plan-Werte =
SUMX (
FCT_TIME_PLANNING_EMPLOYEE_LEGACY,
VAR CurrentEmployee = FCT_TIME_PLANNING_EMPLOYEE_LEGACY[Employee ID]
VAR AvgDailyRate =
CALCULATE (
AVERAGE ( FCT_CONTRACT_EMPLOYEE[daily_rate] ),
TREATAS ( { CurrentEmployee }, FCT_CONTRACT_EMPLOYEE[employee_id] )
)
RETURN
FCT_TIME_PLANNING_EMPLOYEE_LEGACY[PLANNED_WORKING_DAYS] * AvgDailyRate
)
@Anonymous
Please try
Hochrechnung Plan-Werte =
SUMX (
VALUES ( FCT_CONTRACT_EMPLOYEE[employee_id] ),
CALCULATE ( AVERAGE ( FCT_CONTRACT_EMPLOYEE[daily_rate] ) )
* SUMX (
FILTER (
FCT_TIME_PLANNING_EMPLOYEE_LEGACY,
FCT_TIME_PLANNING_EMPLOYEE_LEGACY[EMPLOYEE_ID] = FCT_CONTRACT_EMPLOYEE[employee_id]
),
FCT_TIME_PLANNING_EMPLOYEE_LEGACY[PLANNED_WORKING_DAYS]
)
)
@Anonymous
Please try
Hochrechnung Plan-Werte =
SUMX (
VALUES ( FCT_CONTRACT_EMPLOYEE[employee_id] ),
CALCULATE ( AVERAGE ( FCT_CONTRACT_EMPLOYEE[daily_rate] ) )
* SUMX (
FILTER (
FCT_TIME_PLANNING_EMPLOYEE_LEGACY,
FCT_TIME_PLANNING_EMPLOYEE_LEGACY[EMPLOYEE_ID] = FCT_CONTRACT_EMPLOYEE[employee_id]
),
FCT_TIME_PLANNING_EMPLOYEE_LEGACY[PLANNED_WORKING_DAYS]
)
)
Hey, thanks a lot, it works fine! Using the FILTER function for the lookup and the VALUES function to calculate the average daily rate is a nice way to solve this problem!
You can't use LOOKUPVALUE with a variable, it has to be a base table. Try
Hochrechnung Plan-Werte =
SUMX (
FCT_TIME_PLANNING_EMPLOYEE_LEGACY,
VAR CurrentEmployee = FCT_TIME_PLANNING_EMPLOYEE_LEGACY[Employee ID]
VAR AvgDailyRate =
CALCULATE (
AVERAGE ( FCT_CONTRACT_EMPLOYEE[daily_rate] ),
TREATAS ( { CurrentEmployee }, FCT_CONTRACT_EMPLOYEE[employee_id] )
)
RETURN
FCT_TIME_PLANNING_EMPLOYEE_LEGACY[PLANNED_WORKING_DAYS] * AvgDailyRate
)
Hey john, thank you very much. The solution works perfectly fine, will look into the TREATAS Function for these kinds of problems more.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
17 | |
15 | |
13 | |
10 | |
10 |