Dynamic measure on date slicer

[Krish35]

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 

[v-jayw-msft · Accepted solution]

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))+0

Result 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

[amitchandak]

@Krish35 , No of test passed

countx(summarize(TEST, TEST [ST_ID],TEST [TEST_ID], "_1", calculate(countdistinct([TEST_ID]),Filter(Test,[status]="Pass"))),[_1])

[Krish35]

Sir..Ithink I am not clear in my question..I want the out put something like this ..