DAX to count student record from same column

[Anonymous]

Hi All,

Can I get some help for DAX script

I created a scenario and it is just a simple count.

StudentID	Subject	MeasureName	MeasureValue
1	Chemistry	QuestionsAsked	2
1	Chemistry	QuestionsAnswered	2
1	Biology	QuestionsAsked	1
1	Biology	QuestionsAnswered	1
2	Chemistry	QuestionsAsked	2
2	Chemistry	QuestionsAnswered	1
3	Chemistry	QuestionsAsked	1
3	Chemistry	QuestionsAnswered	1
3	Biology	QuestionsAsked	3
3	Biology	QuestionsAnswered	1

I wanted to count Total number of students whose MeasureValue for QuestionsAnswered  for subject = Measure value QuestionedAsked for that subject

Answer will be 

 Total number of student subjects = 3

because Student ID 2 's Measure value for QuestionsAnswered (Chemistry) is not equal to Measure value QuestionAsked(Chemistry).

Similarly Student ID 3 's mesaure value for QuestionsAnswered (Biology) is not equal to Measure value QuestionAsked(Biology).

I have made those two students records bold to exclude but rest of three records should be compliant.

Can someone guide how a DAX can be written.

Thanks in advance.a

Regards

[Anonymous]

So kind of you @Fowmy 

This DAX is very helpful but requirement is bit different.

• I cannot create an extra column and just wanted to do as measure. is it possible
• I also wanted to check if measure value for QuestionAnswered (chemistry) for same patient = Value for QuestionAsked (Chemistry) for same patient whereas DAX provided is other way around. 
• For example
  • student id 1 's value for QuesionAsked(Chemistry) is equal to value of QuestionAnswered(Chemistry) so count it 
  • Student if 1 's value for QuesionAsked(Biology) is equal to value of QuestionAnswered(Biology) so count it
  • student id 2 's value for QuesionAsked(Chemistry) is not equal to value of QuestionAnswered(Chemistry) so do not count it
  • student id 3 's value for QuesionAsked(Chemistry) is equal to value of QuestionAnswered(Chemistry) so count it
  • student id 2 's value for QuesionAsked(Biology) is not equal to value of QuestionAnswered(Biology) so do not count it
  • So total three counts and answer will be 3

So thankful for your support.

Regards

[Fowmy · Accepted solution]

@Anonymous 

Here is the measure and corrected calculation:

Status Measure = 

SUMX(
    Table1 , 
    VAR __Q = 
        CALCULATE(
            SUM(Table1[MeasureValue]),
            Table1[MeasureName] = "QuestionsAsked",
            ALLEXCEPT(Table1, Table1[StudentID], Table1[Subject] )
        )
    VAR __A = 
        CALCULATE(
            SUM(Table1[MeasureValue]),
            Table1[MeasureName] = "QuestionsAnswered",
            ALLEXCEPT(Table1, Table1[StudentID], Table1[Subject] )
        )
    RETURN
    IF( [MeasureName] = "QuestionsAsked" , IF( __Q = __A , 1 , BLANK()))
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thanks a lot @Fowmy  great skills

[Fowmy]

@Anonymous 

Go to your table in Data view and add this code as a New Column:

Status = 
VAR __A = 
    CALCULATE(
        SUM(Table1[MeasureValue]),
        Table1[MeasureName] = "QuestionsAnswered",
        ALLEXCEPT(Table1, Table1[StudentID], Table1[Subject] )
    )
RETURN
IF(
    IF( [MeasureName] = "QuestionsAsked" , [MeasureValue] -  __A, 0) = 0, BLANK(), [MeasureValue] -  __A
)

You show it in the visual as 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn