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

Dynamic Calculation on date slicer

Hi

I am using a dashboard that shows no.of students(distinct), school-wise(row) and test-wise(column) who have passed certain tests (out of total 5 tests) . We could see the dynamic measure for the no. of students(distinct)in the matrix based on the date slicer. However, I couldn't figure out how to show a dynamic measure in another matrix or a dynamic calculated column value against each student in the student table the no. of unique tests (0 to 5) he has passed for any selected duration of period(from date slicer).

A student may take the test any no. of times an the Table Test may contain the data beyond the selected dates in the slicer. The dynamic measure should only consider the data for the selected dates from the slicer.

 

I have 3 Tables connected with common columns as below:

Table School: SC_ID, SC_Name

Table Student: ST_ID, ST_Name, SC_ID

Table Test: ST_ID,Test_Name, Test_Date, Test Status

 

Thanks in advance.

6 REPLIES 6
v-henryk-mstf
Community Support
Community Support

Hi @Krish35 ,


Based on the information you provided, the description is somewhat complicated. Can you provide the expected results you expect, so that I can do further testing?


Looking forward to your reply.


Best Regards,

Henry

I might have not framed my question with clarity..but I wanted the output like this

 

Capture.JPG

Hi @Krish35 ,


Based on the information you provide, it may not be possible to fully reproduce the results you expect. But according to your needs, I have done the following tests, which can be used as a reference:
Count the students who passed, "pass" is set to 1

v-henryk-mstf_0-1612254798017.png

 


Create a matrix and place the created column in the value position. Put the date in the slicer.

v-henryk-mstf_1-1612254804675.jpegv-henryk-mstf_2-1612254810159.png

By filtering the date, the results are displayed dynamically in the matrix.

v-henryk-mstf_3-1612254817608.png

 

Here is the sample pbix file.


If the established model is not correct, please provide detailed test data and information so that I can do accurate tests. Looking forward to your reply.


Best Regards,
Henry


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

Krish35
Frequent Visitor

please find the link for fake data db_test 

SpiroswayGR
Helper V
Helper V

@Krish35 

Can you share some "fake" data ? 

please find the link for fake data db_test 

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!

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.