Excluding filtered records from calculations

[roosechua]

Hi All,

I would just like to ask for some assistance on a scenario that I am currently working on. I have been thinking how to do this, but somehow, the solution is really out of my depth, so any hint will be appreciated.

I am including here some sample tables, but it does simulate the challenge I am facing.

My sample PBIX file.

There are 3 tables originally:

Table 1: Sections_Questions - This table contains the different sections and the question IDs for each section.

SectionQuestion ID

A	A.1
A	A.2
A	A.3
B	B.1
B	B.2
B	B.3
C	C.1
C	C.2
C	C.3

Table 2: Student_Scope - This table contains the student ID and what sections should be under their scope.

Student IDScope

S001	A,B,C
S002	A,C
S003	B,C

Table 3: Student_Results - This table contains the Student ID, Question IDs and the student grades for each answered question.

Student IDQuestion IDGrade

S001	A.1	80
S001	A.2	84
S001	A.3	90
S001	B.1	84
S001	B.2	87
S001	B.3	83
S001	C.1	89
S001	C.2	90
S001	C.3	82
S002	A.1	87
S002	A.2	84
S002	A.3	85
S002	B.1	89
S002	C.1	91
S002	C.2	89
S002	C.3	84
S003	B.1	78
S003	B.2	79
S003	B.3	82
S003	C.1	79
S003	C.2	80
S003	C.3	81
S003	A.3	90

My sample's data model:

Students are supposed to answer only those questions that are within their scope, e.g. Student 001 answers all questions under sections A, B and C, while Student 002 should answer only questions under sections A and C, etc. However, they might have answered also some questions that are under a section that was not within their scope, so they have a grade for that question, but those questions should be considered as invalid.

When I do my calculations now in Power BI, these invalid questions answered by students that outside of their scope are still showing and affecting the overall output of my computations (I just used an average in the sample PBIX file to show this, but in my real world scenario, I use different measures for this). 

My question now is: Without manually using a slicer, or deleting those invalid records in my source files (as there might be hundreds of them), is there any way that I can exclude these invalid questions from being processed at all by my measures? I tried to think how to do this in DAX, but before I do further experimenting, can this be possible to do using DAX at all?

Thanks in advance for any advice!

[Anonymous · Accepted solution]

HI @roosechua,

Here is the measure formula that I directly use DAX expression to extract the corresponding items and store them in a variable to use it as the condition in grade calculations, you can try it if helps:

formula =
VAR scoreList =
    CALCULATE (
        CONCATENATEX ( VALUES ( Student_Scope[Scope] ), [Scope], "," ),
        FILTER (
            ALLSELECTED ( Student_Scope ),
            [Student ID] IN VALUES ( Student_Results[Student ID] )
        )
    )
VAR questList =
    CALCULATETABLE (
        VALUES ( Sections_Questions[Question ID] ),
        FILTER (
            ALLSELECTED ( Sections_Questions ),
            SEARCH ( [Section], scoreList, 1, -1 ) > 0
        )
    )
RETURN
    CALCULATE (
        SUM ( Student_Results[Grade] ),
        FILTER ( ALLSELECTED ( Student_Results ), [Question ID] IN questList ),
        VALUES ( Student_Results[Student ID] )
    )

Regards,

Xiaoxin Sheng