The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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]))
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.
Solved! Go to 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.
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.
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.
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.
I think you should be writing a measure not column
User | Count |
---|---|
136 | |
119 | |
108 | |
48 | |
41 |
User | Count |
---|---|
220 | |
96 | |
88 | |
82 | |
82 |