Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Filter using a measure with Distinct

Hello

 

I’ve have a table that contains student module data. A student can have multiple modules.  There is a Module_Passed flag that is either 0 or 1

 

There are measures (see attached code) to count all rows, the distinct number of students, the distinct number of student that have Modules that have not been passed and the distinct number of students have passed all modules.

 

The issue is when I grab any of the Distinct measures e.g. the measure that counts the number of students that have passed all modules and then include the modules, I get the number for all passes and not just the count for the passed all modules

 

The pivot grand total is correct, but the values in the body of the pivot are not correct in context

 

Code and output screen prints below... 

 

I'm not sure how to fix the context. All suggestions welcome.

 

 

row_Count:=COUNTROWS(Table1)

Distinct Student_Count:=DISTINCTCOUNT(Table1[Student_ID])

Students with Modules Not Passed:=CALCULATE (
        DISTINCTCOUNT ( Table1[Student_ID] ),
        FILTER ( Table1, Table1[Module_Passed] = 0 )
    )

Students with All Modules Passed:=[Distinct Student_Count]-[Students with Modules Not Passed]

 

 

 

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support

    Hi Anonymous,

     

    Create a measure sa below. If it doesn't meet your requirement, kindly share your excepted result to me.

     

    Students with All Modules Passed2 = CALCULATE(DISTINCTCOUNT(Table1[Student_ID]),ALL(Table1))-CALCULATE(DISTINCTCOUNT(Table1[Student_ID]),FILTER(ALL(Table1),Table1[Module_Passed]=0))

     

    Regards,

    Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support

      Hi Anonymous,

       

      Does that make sense? If so, kindly mark my answer as a solution to close the case.


      Regards,
      Frank