Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@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 ..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |