Forum Discussion
Helper IVAverage of multiple values from different table
Morning!
I'm trying to get data from one table into another. I use a bridge table and the relationship looks like this:
Unit Performance has a list of students, and all the units they have been on. Pivoted has a list of students. This is a many to many relationship, so I'm using a bridge table. What I want is to take a field in Unit Performance called "FInal Unit Mark", and get the average for each student over in Pivoted, in the "Average Unit Mark" column. So far I've got this:
Average Unit Mark =
VAR unitMark = SUMMARIZE(
'Unit Performance Dataset',
'Unit Performance Dataset'[Linked Unit Code],
"Unit Mark",
AVERAGE('Unit Performance Dataset'[Unit Mark Final]))
RETURN
CALCULATE(
unitMark,
FILTER(
ALL('Unit Performance Dataset'),
Pivoted[INSTITUTION_ID] = SELECTEDVALUE('Unit Performance Dataset'[INSTITUTION_ID])
)
)
But this gives me a "multiple columns cannot be coverted to a scalar value" error. I feel like I'm missing something out here.
2 Replies
avatorlImpactful Individual
https://docs.microsoft.com/en-us/dax/calculate-function-dax
First parameter of DAX CALCULATE functions should be an expression to be evaluated. But you used a table (unitMark variable is just a table) instead of expression.
You can use something like COUNTROWS(table) as an expression. But the table itself is not an expression.- amitchandak
Super User
EpicTriffid , I did not get the reason you try to filter pivoted table INSTITUTION_ID, there is no role in table .
Why not bridge table is used for the slicer .
What is the need to filter the pivoted table. Or you want only those Id present in pivoted table ?
