Function RELATED expects a fully qualified column reference as its argument

[cwozniak03]

Hi,

I am trying to do a DAX calculation with filtered data using the following DAX:

College_Totals_No_Loans = CALCULATE(SUM('All Grant Amounts'[grant_amount]),FILTER('All Grant Amounts', 'All Grant Amounts'[student_number] = EARLIER('student_colleges'[student_number]) && (('All Grant Amounts'[college_id] = EARLIER('student_colleges'[college_id]) || 'All Grant Amounts'[college_id] = 0) && RELATED('funding_types'[funding_type_id] <> 3))))

I have used this calulation elsewhere and it is working fine, just without the RELATED portion. However I need to filter out any results that have a funding_type_id of 3 hence my bolded addition.

When I put this in , I get the following error:

Function RELATED expects a fully qualified column reference as its argument

The funding_types table is indeed related to the All Grant Amounts table in the table relationship tab and I used intellisense to type the table name and it is clearly fully qualified.

Any ideas why I am seeing this error?

Thanks,

Chris

[OwenAuger]

Hi Chris,

At a glance, it appears to be a misplaced bracket causing the error.

Try changing

&& RELATED('funding_types'[funding_type_id] <> 3)

to

&& RELATED('funding_types'[funding_type_id]) <> 3

Please post back if that doesn't fix it

Regards,

Owen

[cwozniak03]

Ha, thanks that was dumb.

It did fix the fully qualified error, but gives me a new error stating that the column either doesnt exist or douesnt have a relationship to any table in the current context even though it does:  PS I also tried grants[funding_type_id] as I just realized when posting this image that I dont need the ID from the funding types table.

[OwenAuger · Accepted solution]

Hi again @cwozniak03 

1. @v-frfei-msft 's reply is correct - RELATED cannot traverse a many-to-many relationship.
2. I take it you creating a calculated column in the 'All Grant Amount's table, rather than a measure? It would appear so based on the use of EARLIER. It might be considered better practice to create a measure, depending how the result of this calculation will be used, but I won't dwell on that point here.
3. Regardless of whether you create a calculated column or measure, I believe your formula can be written more simply by applying filters to individual columns rather than using the FILTER function over the 'All Grant Amounts' table, and there is no need to use the RELATED function to do this. If this is a calculated column, I would probably write an expression structured like this using variables to store values from the current row and then applying individual column filters within CALCULATE:
   

   College_Totals_No_Loans =
   VAR CurrentStudentNumber = 'All Grant Amounts'[student_number]
   VAR CurrentCollegeID = 'All Grant Amounts'[college_id]
   RETURN
       CALCULATE (
           SUM ( 'All Grant Amounts'[grant_amount] ),
           'All Grant Amounts'[student_number] = CurrentStudentNumber,
           'All Grant Amounts'[college_id] IN { CurrentCollegeID, 0 },
           'funding_types'[funding_type_id] <> 3,
   
           // Clear all filters on the table, 
           // since CALCULATE adds all values from current row to filter context
           // Filters specified above will still apply
           ALL ( 'All Grand Amounts' )
       )
   

The above expression might not quite do what you want (I made a few guesses about the logic), but I think this is the sort of structure you should use.

Could you describe roughly in words what the calculation should be returning, perhaps with some sample data?

Regards,

Owen

[cwozniak03]

Owen,

Thank you so much for this, I am just starting out with PowerBI and this is great to know that you can do things like this.

Also good to know that RELATED doesnt work with M2M.

Chris

[v-frfei-msft]

Hi @cwozniak03 ,

As the screenshot you shared. The relationship between tables are M2M. That should be the case. Is it possbile to change it to 1:N or create a bridge table to work on it?