Dynamic Calculation on date slicer

[Krish35]

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.

[v-henryk-mstf]

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

[Krish35]

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

[v-henryk-mstf]

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

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

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

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]

please find the link for fake data db_test 

[SpiroswayGR]

@Krish35 

Can you share some "fake" data ? 

[Krish35]

please find the link for fake data db_test