Forum Discussion

Krish35's avatar
Krish35
Frequent Visitor
5 years ago
Solved

Dynamic measure based on a date slicer

Hi everyone !

I am new to power bi and started using it very recently. Could someone help me ?  I have a table which records the date-wise attempts of the tests taken and their status. There are no restrictions on the no. of attempts for the tests. I am struggling to get the solution in a scenario, that requires the no. of unique students who have taken the tests during a specific date periods (Date is used as slicer) and passed. The table looks like this:

 

NameLesson_IDTest Attempted DateStatus
AnilL101-01-2021fail
RamuL201-01-2021pass
AnilL102-01-2021pass
KrisL102-01-2021pass
SivaL102-01-2021fail
KrisL202-01-2021pass
AnilL203-01-2021pass
RamuL103-01-2021fail
SivaL203-01-2021pass
KrisL103-01-2021pass
KrisL204-01-2021fail
SivaL104-01-2021pass

 

For example, If I select Date range from 01.01.2021 to 02.01.2021, the result should be like :

NameL1L2
Anil10
Ramu01
Kris11
Siva00

 

And, If I select Date range from 01.01.2021 to 04.01.2021, the result should be like:

NameL1L2
Anil11
Ramu01
Kris11
Siva11

 

Thanks in advance.

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Krish35 

    Create a measure as shown below, insert a matrix visual and plot the data as follows :

     

    Result = 
CALCULATE(
    DISTINCTCOUNT(T4[Name]),
    T4[Status]="Pass"
)

     

5 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Krish35 

    Create a measure as shown below, insert a matrix visual and plot the data as follows :

     

    Result = 
CALCULATE(
    DISTINCTCOUNT(T4[Name]),
    T4[Status]="Pass"
)

     

    • Krish35's avatar
      Krish35
      Frequent Visitor
      5 years ago

      Thank you Fowmy. It worked for me.  Is it possible to show a measure or calculated column for the no. of students(distinctcount) who have passed any one or any two or any three...any ten tests from a list of 10 test IDs i.e.,  L1,L2,L3..upto L10 (used as slicer), for a date period(date slicer). Student passing same test twice or more to be treated as one. 

      Thanks again.

      • vanessafvg's avatar
        vanessafvg
        Icon for Community Champion rankCommunity Champion
        5 years ago

        do you need a count of the tests or of the student?  I would think that measure should work if you just want to count the student for any pass that has been had

    • Krish35's avatar
      Krish35
      Frequent Visitor
      5 years ago

      Thank you..but the student 'Kris' showing twice under test 'L1' for the date period 01.01.2021 to 04.01.2021..it should show as only 1.