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. StudentID Subject MeasureName MeasureValue 1 Chemistry QuestionsAsked 2 1 Chemis...
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



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