Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

1 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()))
)

Fowmy_0-1600686524232.png

 

________________________

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



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

@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()))
)

Fowmy_0-1600686524232.png

 

________________________

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



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Thanks a lot @Fowmy  great skills

Fowmy
Super User
Super User

@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 

Fowmy_0-1600662258187.png

________________________

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

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors