Forum Discussion
Dynamic measure on date slicer
Hi everyone .
We have 3 Tables connected with common columns something like below:
1.SCHOOL : SC_ID, SC_Name
2.STUDENT : ST_ID, ST_Name, SC_ID
3.TEST : ST_ID,TEST_ID, TEST_DT, Status
We are using a dashboard that shows no.of students(distinct), school-wise(row) and test-wise(column) who have passed the tests. However, we couldn't figure out how to show a dynamic measure that shows school-wise no. of students who have passed the no. of unique tests (L1, L2..... L5) for any selected duration of period(from date slicer). There are a total of 5 tests and a student may take the tests any no. of times. The TEST table contain the data upto today, but the dynamic measure should only consider the data for the selected dates from the slicer.
Please find here the link for 'fake data' https://1drv.ms/u/s!AhcgC9ElOKj_gqFiLoZzAa03gdcgpQ?e=gubWAh
Hi Krish35 ,
Check the measures.
countpasstest = CALCULATE(COUNT(TEST[TEST_ID]),FILTER(TEST,TEST[Status]="pass")) 0test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=BLANK()))+0 1test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=1))+0 2test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=2))+0 3test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=3))+0 4test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=4))+0 5test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=5))+0Result would be shown as below.
If you want let's say any 4 test contains any 3 test, you could use ">" instead of "=".
Best Regards,
Jay
3 Replies
- amitchandak
Super User
Krish35 , No of test passed
countx(summarize(TEST, TEST [ST_ID],TEST [TEST_ID], "_1", calculate(countdistinct([TEST_ID]),Filter(Test,[status]="Pass"))),[_1])
Sir..Ithink I am not clear in my question..I want the out put something like this ..
Hi Krish35 ,
Check the measures.
countpasstest = CALCULATE(COUNT(TEST[TEST_ID]),FILTER(TEST,TEST[Status]="pass")) 0test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=BLANK()))+0 1test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=1))+0 2test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=2))+0 3test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=3))+0 4test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=4))+0 5test = CALCULATE(DISTINCTCOUNT(STUDENT[ST_ID]),FILTER(STUDENT,[countpasstest]=5))+0Result would be shown as below.
If you want let's say any 4 test contains any 3 test, you could use ">" instead of "=".
Best Regards,
Jay
