undefined

[RandomUser7117]

I have two Excel Sheets:

Sheet 1 has the following Columns:

1. Country

2. StudentName

3. Subject

4. Scores

Sheet 2 has the following Columns:

1. Country

2. StudentName

3. Subject

4. PeerStudents

In PowerBI, I want to achieve the following:

Add 3 Slicers from sheet 2, Country, StudentName, Subject, which will fetch the names of peer students from the PeerStudent column

Now, Use the list to match with the StudentName from the Sheet1 and fetch the scores.

The problem is, Lets say, For "Mexico", I have a student named "John" who has given exam in "English". The Peer Students are "Tom", "Ron", "Harry", "Angela"

Now in from Sheet 1, I want the Scores of "Tom", "Ron", "Harry", "Angela" for "Mexico" in "English". 

Also note, that There are multiple teachers giving scores, so lets say for Angela, She have have 5 different scores given by 5 different teachers

I assume if there is a way to create a temporary table which can be used to VLOOKUP and fetch scores.

[v-zhengdxu-msft]

Hi @RandomUser7117 

Here I create a set of sample:

Sheet1:

Sheet2:

Then add a measure:

MEASURE =
VAR _Country =
    SELECTEDVALUE ( Sheet2[Country] )
VAR _StudentName =
    SELECTEDVALUE ( Sheet2[StudentName] )
VAR _PeerStudents =
    CONCATENATEX (
        FILTER (
            ALLSELECTED ( Sheet2 ),
            'Sheet2'[Country] = _Country
                && 'Sheet2'[StudentName] = _StudentName
        ),
        'Sheet2'[PeerStudents]
    )
RETURN
    IF (
        CONTAINSSTRING ( _PeerStudents, SELECTEDVALUE ( 'Sheet1'[StudentName] ) ),
        SUM ( Sheet1[Scores] )
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RandomUser7117]

Hi @v-zhengdxu-msft , thanks for the reply. I used your method this is creating a small mismatch. So, in the second table (the table with the measure), The filter is not filtering the Subjects. As you can see the picture attached by you, you have selected English, yet It is showing scores for all the subjects. May you help me in fixing it?

[v-zhengdxu-msft · Accepted solution]

Hi @RandomUser7117 

Here I add a Slicer 2, just as the following picture shows:

The Slicer 1 is to filter the Sheet2(It is designed to help you screen students in Sheet2, so that it can not affect the output table visual)

You can add a Slicer 2 with the field [Subject] from Sheet1, the result is as follow:

It is worth noting that the sample is very simple, there are a lot of details that you still need to optimize.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@RandomUser7117 , Do not join these tables. Use all slicer on duplicate table of Table1

Plot this measure with columns of table1.

If required remove column consition from _peer var or make and(&&) as or (||)

Peer score =
Var _peer = SUMMARIZE(filter(Table2, Table2[StudentName] in values(Tabledup[StudentName] ) && Table2[Country] in values(Tabledup[Country] ) && Table2[Subject] in values(Tabledup[Subject] )), Table2[StudentName])
return
calculate(sum(Table1[Scores]), filter(Table1,   Table1[StudentName] in _peer && Table1[Country] in values(Tabledup[Country] ) && Table1[Subject] in values(Tabledup[Subject] ))  )