Forum Discussion
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-msftCommunity 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-msftCommunity Support
Hi Anonymous,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank