Filter using data from different table

[vanquisher11]

I want to count the percentage of pass for sciences students

column 0                  column 1      column 2      column 3       column 4

student 1-sciences      student 1     maths              80                  failed

student 2-sciences      student 2     maths              97                  passed

student 3-sciences      student 3     science             93                  passed

student 4-arts             student 4     history             89                  passed

student 5-arts             student 5     literature          61                  failed

column 0,1, and 2 are from Table A-categories

column 3, and 4 are from Table B- results.

so basically the formula should be similar to this

percent= (divide(calculate(countrow(column 0),column 2="maths"||"science", column 4="passed"),calculate(countrow(coumn 0), column 2= "maths"||"sciences"))

and the answer is meant to be 66.67%

Sadly, I can't seem to use column 4 as a filter as it's from a different table. I keep getting 100% results.

How do I go around this issue?

Thank you for your help!

Kind Regards,

Matt

[v-yuezhe-msft]

@vanquisher11,

How about you use Merge Queries feature in Query Editor of Power BI Desktop to merge all the required columns into one new query? And what is the result do you want to get when using column 4 filter?

Regards,
Lydia

[jthomson]

Try making a measure to count the number of rows of sciences, something like:

NumberofSciences = calculate(countrows(TableA),column2="maths"||column2="science")

Thense probably need to know a bit more of how your tables A and B are related, but the idea would be to divide the above measure by itself, but on the first option you put it in a calculate function using the related function to look up column 4