Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cwozniak03
New Member

Function RELATED expects a fully qualified column reference as its argument

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
 
1 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

 

 

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

5 REPLIES 5
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.

image.png

 

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

 

 

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

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?

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.