cancel
Showing results for
Did you mean:
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 RefNo School School_Code Diet_Year School_Key Pupil_Code Award Subject Stage 1 School 1 M001 2019 M001_2019 Pupil_001 1 English S4 2 School 1 M001 2019 M001_2019 Pupil_002 1 Maths S4 3 School 1 M001 2019 M001_2019 Pupil_003 2 English S4 4 School 1 M001 2019 M001_2019 Pupil_004 2 English S4 5 School 1 M001 2019 M001_2019 Pupil_005 3 Maths S4 6 School 2 M002 2019 M002_2019 Pupil_006 1 English S4 7 School 2 M002 2019 M002_2019 Pupil_007 2 English S4 8 School 2 M002 2019 M002_2019 Pupil_008 2 Maths S4 9 School 2 M002 2019 M002_2019 Pupil_009 3 English S4 10 School 2 M002 2019 M002_2019 Pupil_010 3 English S4

 Table: DimRolls School_Key School_Code Year S4_Roll M001_2019 M001 2019 10 M002_2019 M002 2019 12

 Relationship: 1 to * on School_Key

 School 1 Graded_Entries Subject Awards 1 & 2 % of S4 Roll English 3 30 Maths 2 20

 School 2 Graded_Entries Subject Awards 1 & 2 % of S4 Roll English 4 33.3 Maths 1 8.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.

1 ACCEPTED SOLUTION
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.

Regards

Geoff

7 REPLIES 7
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")```

Regard,

Chen

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]))`

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

Chen

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

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.

Regards

Geoff

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.

Hope this is what you want!

Have a nice day,

Chen

Announcements