I've got two tables, one with 640 records, one with ~14,000. The 640 are a subset of the 14,000, but from a different database. There is a common field, call it User ID. I've set up a relationship between the two tables - BI tells me it can only be Many: One.
I am now trying to do math with two separate fields. I created a new column in Table A, and tried to insert the expression ='TableB'[ColumnB] / 'TableA'[ColumnA]. Two things: First, in the autocomplete dropdown menu, when I hit ', only entries for TableA autopopulate, leading me to think there's an issue with recognizing the relationship between the two tables. Second, when I finish the expression and hit enter, I get the error message "A single value for column 'UserCount' in table 'ViewAccounts' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
I'm not sure what this means or why I am not able to complete this operation. There shouldn't be a single value for UserCount, each record has a different count. That's the point.
I'm assuming it doesn't recognize the relationship between the two columns, and so it won't do the math for the records that match between tables. I thought I was doing this correctly, but apparently not. Any help is appreciated. Thank you!