cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GCC
Frequent Visitor

New Measure to pick up a value in another table

Hello,

I'm a newby to Power BI and Dax and struggling with what is probably a very simple piece of code.

I have two tables: FactSQA and DimRolls joined by a one to many relationship on 'School_Key'.

The FactSQA table contains the SQA awards (1, 2 or 3) for each pupil at each school.

The DimRolls table contains the rolls for the S4 classes in each school.

I've created a measure which counts the number of 1 & 2 awards for each subject (English & Maths) within each school.

I now need to take this number and divide it by the class roll held in the DimRolls table, then times by 100 to obtain the percentage. Obtaining the class rolls is causing me difficulties. Can anyone help?

 

Table: FactSQA        
RefNoSchoolSchool_CodeDiet_YearSchool_KeyPupil_CodeAwardSubjectStage
1School 1M0012019M001_2019Pupil_0011EnglishS4
2School 1M0012019M001_2019Pupil_0021MathsS4
3School 1M0012019M001_2019Pupil_0032EnglishS4
4School 1M0012019M001_2019Pupil_0042EnglishS4
5School 1M0012019M001_2019Pupil_0053MathsS4
6School 2M0022019M002_2019Pupil_0061EnglishS4
7School 2M0022019M002_2019Pupil_0072EnglishS4
8School 2M0022019M002_2019Pupil_0082MathsS4
9School 2M0022019M002_2019Pupil_0093EnglishS4
10School 2M0022019M002_2019Pupil_0103EnglishS4

 

 

Table: DimRolls  
School_KeySchool_CodeYearS4_Roll
M001_2019M001201910
M002_2019M002201912

 

Relationship: 1 to * on School_Key

 

School 1Graded_Entries 
SubjectAwards 1 & 2% of S4 Roll
English330
Maths220

 

School 2Graded_Entries 
SubjectAwards 1 & 2% of S4 Roll
English433.3
Maths18.3

 

Measure:Awards 1 and 2 = Calculate(Count(FactSQA[Pupil_Code]),(FactSQA[Award]=1)||(FactSQA[Award]=2))
Measure Required:(Awards 1 and 2) / S4_Roll *100

 

It's the '% of S4' which I'm trying to calculate.

 

Thanks in advance.

 

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
GCC
Frequent Visitor

Hi Chen,

I had a year slicer on my report which was inpacting adverseley on the output.

Your code is working just fine.

Many thanks for your help.

Regards

Geoff

View solution in original post

7 REPLIES 7
GCC
Frequent Visitor

Hi Chen,

Many thanks for such a quick response.

When I use SUM in my code it sums the S4 roll for both schools (12 + 10) and therefore calculates the wrong percentage figure. How best should I tweak the code so that it only picks up a single school roll?

Regards

Geoff

Anonymous
Not applicable

Hi Geoff,

 

You are welcome 😉 ,

actually it depends on how will you show the result, 

If you want to calculate the result separately,

you could write two measure for each school,

 

%S4_School1 = CALCULATE(DIVIDE( FactSQA[Awards 1 and 2];SUM(DimRolls[S4_Roll]));FactSQA[School_Code]="M001")

%S4_School2 = CALCULATE(DIVIDE( FactSQA[Awards 1 and 2];SUM(DimRolls[S4_Roll]));FactSQA[School_Code]="M002")

Hope this would be helpful,

 

Regard,

Chen

GCC
Frequent Visitor

Hi Chen,

I have a slicer at the top of the page so that the user can select their school. Therefore I need the code to work on all schools. 

When I try and use your new code, I'm prevented from entering FactSQA[School_Code]=DimRolls[School_Code].

Do you have any further ideas?

Regards

Geoff

Anonymous
Not applicable

Hi Geoff,

 

If you have already a slicer of School, the first code shold be working.

 

 

%S4 = DIVIDE( FactSQA[Awards 1 and 2];SUM(DimRolls[S4_Roll]))

Capture.PNG

 

Can I ask why do you need use FactSQA[School_Code]=DimRolls[School_Code] ? 😉

 

Chen

 

GCC
Frequent Visitor

Hi Chen,

The first code you gave me is summing the S4 roll for all schools and not the single school selected in the slicer.

Regards

Geoff

 

GCC
Frequent Visitor

Hi Chen,

I had a year slicer on my report which was inpacting adverseley on the output.

Your code is working just fine.

Many thanks for your help.

Regards

Geoff

View solution in original post

Anonymous
Not applicable

Hi there,

 

To calculate the %S4_Roll, you can simply write :

 

%S4 = DIVIDE(FactSQA[Awards 1 and 2];SUM(DimRolls[S4_Roll]))

Then you can get the %, and present them in a table.

Screenshot 2019-08-07 at 20.42.11.png

Hope this is what you want!

Have a nice day,

Chen

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors