Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX to count student record from same column

Hi All,


Can I get some help for DAX script

 

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

StudentIDSubjectMeasureNameMeasureValue
1ChemistryQuestionsAsked2
1ChemistryQuestionsAnswered2
1BiologyQuestionsAsked1
1BiologyQuestionsAnswered1
2ChemistryQuestionsAsked2
2ChemistryQuestionsAnswered1
3ChemistryQuestionsAsked1
3ChemistryQuestionsAnswered1
3BiologyQuestionsAsked3
3BiologyQuestionsAnswered1

 

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

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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



4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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

     



  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Fowmy
      Super User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        Thanks a lot Fowmy  great skills