help with DAX measure

[Anonymous]

Hi All,

I am building a PowerBi report, sourcing data from a PowerBI dataset. Hence there are no options available to change the data model. Below sample dataset records the exams taken by a list of students. The requirement is to only list the students that have only taken 'maths' exam. In this dataset, only student 4 should come in the output. Is there a way to achieve this in DAX, rather than changing the data model?

Student No	Exam
1	maths
1	english
1	science
2	science
3	english
3	maths
4	maths

Regards,

Murali

[daxer-almighty · Accepted solution]

Of course there is. You have to write a measure that will return 1 for the rows you want to keep and 0 for the others. Then use the measure as the filter in the Filter Pane of your visual. Very simple. Here's something to get you started:

// Assuming that your table as shown is 
// T, you can have a measure:

[Taken Only Maths?] =
var __studentNumber = selectedvalue( T[Student No] )
var __exams =
    FILTER(
        ALL( T ),
        T[Student No] = __studentNumber
    )
var __takenMath =
    0 < countrows(
        filter(
            __exams,
            T[Exam] = "maths"
        )
    )
var __notTakenNonMaths =
    0 = countrows(
        filter(
            __exams,
            T[Exam] <> "maths"
        )
    )    
return
    int( __takenMath && __notTakenNonMaths )