Forum Discussion

Haripoola's avatar
Haripoola
Frequent Visitor
4 years ago

Count values over a period

Hi team,

I have a fact table which contains Student's details like below (attached sample copy), 
I'm trying to create a DAX expression that will enable me to return data when a student failed/passed status is in between a selected date range.
For example consider HARRY Data from below table When i select a date range of 01/01/19 to 31/05/21 using a date slicer, He should be treated as failed student and his count should be in Failed count, If i move slicer to 06/01/21 His count should be removed from Failed count and should be added in Passed student as he passed the exam on 06/01/21.
Please let me know if needed more information.

 

 

COUNTROWS
DAX
status change

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Haripoola , Refer

     

    meausre =
    var _max = maxx(filter(allselected(Table), Table[StudentID] = max(Table[StudentID])), Table[StudentUpdatedon])
    return
    calculate(count(Table[StudentID]), filter(Table, Table[StudentUpdatedon] =_max))

     

    or


    Measure =
    VAR __id = MAX ('Table'[StudentID] )
    VAR __date = CALCULATE ( MAX('Table'[StudentUpdatedon] ), ALLSELECTED ('Table' ), 'Table'[StudentID] = __id )
    CALCULATE (count(Table[StudentID]), VALUES ('Table'[StudentID] ),'Table'[StudentID] = __id,'Table'[StudentUpdatedon] = __date )

    • Haripoola's avatar
      Haripoola
      Frequent Visitor
      4 years ago

      amitchandak 

      Thank you for your solution and I think it works for my requirement.

      Could you please suggest me how to calculate with combination of StudentID and StudentName.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Haripoola , 

    Please refer to my pbix file to see if it helps you.

    Create a measure.

     

    Measure= calculate(DISTINCTCOUNT('Table'[StudentID]),FILTER(('Table'),'Table'[ExamStatus]=SELECTEDVALUE('Table'[ExamStatus])))

     

     

     

     

     

     

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Haripoola ,

    Does that make sense? If so, kindly mark my answer as the solution to close the case and help more people find the answer please. Thanks in advance. Or if you have resolved the issue, please share your way.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.