Calculated field on dimension table from fact table

[michaelccdf]

I have two tables, Attendance and Students. 

Attendance

Date	Period	CourseID	StudentID	IsAbsent
2020-01-01	1	Math	1	0
2020-01-01	1	Math	2	1
2020-01-01	2	Science	1	1

Students

StudentID

1

2

I would like to add a calculated column to the Students table showing the total number of periods absent:

AbsentPeriods = CALCULATE(SUM(Attendance[IsAbsent]),Attendance[StudentID] = EARLIER(Students[StudentID]))

This works fine except that it does not incorporate any of the filters in the report (user filters). If, for example, I want to see the number of periods absent for each student (to create a histogram) for Math only, the Students.PeriodsAbsent field does not take this into account.

I tried using KEEPFILTERS but this does not work either:

AbsentPeriods = CALCULATE(SUM(Attendance[IsAbsent]),KEEPFILTERS(Attendance[StudentID] = EARLIER(Students[StudentID])))

How can I keep the filters in the report to calculate the column in the Students table ?

UPDATE

As mentioned in the accepted answer, calculated columns are not re-evaluated in the current filter context so these won't do what I am looking for.  Measures are re-evaluated, however as far as I know, there is no way to create a table of measures and then put visuals on top of it.  With these two constraints in mind, I've done the following to get what I'm looking for:

1) Create calculated columns with filters hard-coded in.  For example if I want to see the total math absences for each student:

AbsentPeriodsMath = CALCULATE(SUM(Attendance[IsAbsent]),Attendance[StudentID] = EARLIER(Students[StudentID]),Attendance[CourseID]="Math")

I can now use this column as a dimension to answer questions like: Do students with high absenteeism in Math also have high absenteeism in Science?

2) Use python visualizations.  PowerBI feeds a table to python which can then be further manipulated and then visualized.  I've used this to create histograms.

[Icey]

Hi @michaelccdf ,

Based on my understanding, it is not necessary to create calculated columns or measures in your scenario.

Try this:

Create a table visual like so:

Then, you can keep filters working.

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[michaelccdf]

Thanks, this table visual is sort of what I am trying to get to but, I then I want to go a step further.  For example, I would like to group the students based on the SUM(IsAbsent) Column into High Absenteeism and Low Absenteeism students and then use this column to filter the Attendance table. 

This situation is similar to having a sales and a products table: I would want to have a total sales column in the products table so that I could then group them into best selling, average selling and worst selling groups and then filter the sales table with these groups. 

[Icey · Accepted solution]

Hi @michaelccdf ,

I know what you want, but the values of calculated columns do not change with different filters. They are fixed, but measures are not. So, creating Measures is the best option. 

Please check if this could meet your requirements.

1. Create Measures.

AbsentPeriod = SUM(Attendance[IsAbsent])

Absenteeism Level = IF ( [AbsentPeriod] >= 1, "High Absenteeism", "Low Absenteeism" )

2. Put "Absenteeism Level" measure on the visuals you want to filter.

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

I think you should be writing a measure not column