Reply
TravellingMagpi
Regular Visitor
Partially syndicated - Outbound

Create a calculated column based on mutiple fields, then a measure to determine %

Hi, I'm looking to create a calculated column to track student reading progress by year, month, grade in both English and Arabic subjects - a simple yes / no will suffice.

 

Monthly we have a reading extract provided with the relevant fields appended to the previous months data which includes the current reading level of each student in each subject.  All of these are text fields.

@Reading Summary 

We have a seperate table which includes the Target Reading levels by year, month, grade, subject and scale.  As the targets and reading levels are text values, we included the field Scale to provide a numeric value.

@Reading Target 

 

So i need to add the calculated column to the monthly extract which checks the current English Reading Level against the target to determin yes / no, also checks the current Arabic Reading Level against target to determine yes / no.

 

Finally a meausre to calculate a what % of students are at the expected reading level.

 

Any assistance appreciated.  

 

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

Syndicated - Outbound

If it's a requirement to compare the reading level with a !=  (as opposed to scoring), the easiest way to return the Target from the Target table to the Summary table will be to use a LOOKUPVALUE.  I've had a look at the data and you may need to standardise a couple of things to make that work:

Academic Year has '_' and '-' in different tables, for example, 2023-2024. Choose one format and change the data.

You have Class in one table and Grade in the other. You might want to call them the same thing.

If you need to return a numeric value, that will be achievable but you will want to have a number in the Summary table too.  Just add a custom column in Power Query with the logic.  Alternatively, create a dimension table which matches  grade term (for example, Ruby) with numeric value.

That should be enough to get you started

View solution in original post

1 REPLY 1
HotChilli
Super User
Super User

Syndicated - Outbound

If it's a requirement to compare the reading level with a !=  (as opposed to scoring), the easiest way to return the Target from the Target table to the Summary table will be to use a LOOKUPVALUE.  I've had a look at the data and you may need to standardise a couple of things to make that work:

Academic Year has '_' and '-' in different tables, for example, 2023-2024. Choose one format and change the data.

You have Class in one table and Grade in the other. You might want to call them the same thing.

If you need to return a numeric value, that will be achievable but you will want to have a number in the Summary table too.  Just add a custom column in Power Query with the logic.  Alternatively, create a dimension table which matches  grade term (for example, Ruby) with numeric value.

That should be enough to get you started

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)