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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jrmaier
Helper IV
Helper IV

column value based on the values from two other columns

Hi, I am looking for help if it is possible to add a column value based on the values from two other columns.

I have two possible values for the Class Name column based on the Course Name and whether a student is in 7 or 8 Grade.

The Class Name values need to be either "7 Mathematics" or "8 Mathematics". The first image below is what I am hoping for it to look like.

 

jrmaier_0-1679495720453.png

 

If this addition below can be added it would be fantastic!

If the Course Name value is any of these with the green checks that name would just be duplicated in the Class Name column.

jrmaier_1-1679496241837.png

So basically copy the Course Name values to the Class Name column unless they are math classes and then assign either of the two corrected values.

 

 

1 ACCEPTED SOLUTION

1. Identify a primary key in your data.  In your case that is a composite key made up of Course Name and Student Grade which I implemented as a calculated column

2. Right click on that primary key column and create (or edit) a group. Now you can bucket collections of primary keys into your Class groups.

3. Use the Class groups column in your visuals.

 

The same can be achieved with lots of code in either Power Query or DAX, but the column group method is more convenient, and it is sufficient if you don't expect your data to change frequently.

View solution in original post

8 REPLIES 8
jrmaier
Helper IV
Helper IV

@lbendlin Thank you very much for your continued help. Below is some sample data with the results field being the Class Name column. Most of the values are the same as the Course Name column. All students need to be in either 7 Mathematics or 8 Mathematics based on their grade level and regardless of what actual math class they are in.

 

Course NameStudent GradeClass Name
7 English77 English
7 Integrated Science77 Integrated Science
7 Language Arts77 Language Arts
7 Mathematics77 Mathematics
7/8 Algebra I77 Mathematics
7/8 Algebra I88 Mathematics
8 English88 English
8 Integrated Science88 Integrated Science
8 Language Arts88 Language Arts
8 Mathematics88 Mathematics
Algebra II/Trig88 Mathematics
Hon. Algebra II/Trig88 Mathematics

see attached

@lbendlin That looks fantastic! I appreciate you. Could you give me a quick explanation of how the "Class Name" column gets the values?

1. Identify a primary key in your data.  In your case that is a composite key made up of Course Name and Student Grade which I implemented as a calculated column

2. Right click on that primary key column and create (or edit) a group. Now you can bucket collections of primary keys into your Class groups.

3. Use the Class groups column in your visuals.

 

The same can be achieved with lots of code in either Power Query or DAX, but the column group method is more convenient, and it is sufficient if you don't expect your data to change frequently.

jrmaier
Helper IV
Helper IV

@lbendlin I did some reading on that and appreciate you steering me to a totally new area that I have not used in PBI before. I still cannot figure out how to apply that to my desired outcome.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

jrmaier
Helper IV
Helper IV

@lbendlin thanks, I will check it out.

lbendlin
Super User
Super User

Read about Column Groups.  Much easier to create and maintain.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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