cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krish35
Frequent Visitor

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 

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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.

2.PNG

3.PNG

If you want let's say any 4 test contains any 3 test, you could use ">" instead of "=".

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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.

2.PNG

3.PNG

If you want let's say any 4 test contains any 3 test, you could use ">" instead of "=".

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

amitchandak
Super User IV
Super User IV

@Krish35 , No of test passed

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 Capture.JPG

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.