RELATEDTABLE with ALL

[Power-User]

Hi,

I am trying to add ALL() into the DAX statment to get the total present marks in the attendance table only were they match in the pupil table. I have a primary key link between the 2 tables.

I need the total present marks to not change if a filter is used.

CALCULATE(SUM('Attendance (Table)'[Present]),ALL('Attendance (Table)'),RELATEDTABLE('Pupil (Table)'))

Any help greatly appreciated.

Many tahks

Chris

[Anonymous · Accepted solution]

Hi @Power-User ,

From your description, it seems like you want to ignore any filters that might be applied to the 'Attendance (Table)' while still maintaining the relationship with the 'Pupil (Table)'. To achieve this, you should use the `ALL` function to remove filters from the 'Attendance (Table)' and then use `RELATEDTABLE` to bring in the related data from the 'Pupil (Table)'. However, the `RELATEDTABLE` function is typically used to create a one-to-many relationship in the opposite direction, from the one side to the many side.

Here's a step-by-step action plan to correct your DAX formula:
1. Understand the Data Model: Ensure that the relationship between 'Attendance (Table)' and 'Pupil (Table)' is correctly set up with a primary key in the 'Pupil (Table)' that uniquely identifies each pupil.

2. Modify the DAX Formula: You'll want to use the `CALCULATE` function to sum the present marks, remove filters from the 'Attendance (Table)' using `ALL`, and ensure that the relationship with the 'Pupil (Table)' is maintained.

3. Your DAX formula should look something like this:

TotalPresentMarks =
CALCULATE (
    SUM ( 'Attendance (Table)'[Present] ),
    ALL ( 'Attendance (Table)' )
)

This formula will calculate the total present marks regardless of any filters applied to the 'Attendance (Table)'.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.