Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have the following data where I have students and the courses they opted for and the status '1' represents pass and '0' represents fail. I want to show a cumulative course status view, where if I select student s1 I should show 3 courses passed and select s1 and s2 should show 4 courses passed (A, B, C, D)
1) if I select s1 the cumulative pass should show 3 (since s1 passed in A, B, and D)
2) if I select s1 and s2 the cumulative pass should show 4 (since s1 passed in A, B, D & s2 in B, C).
3) if I select s1, s2 & s3 the cumulative pass should still show 4 (since s1 passed in A, B, D & s2 in B, C & s3 in A, C, D)
4) if I select s1 and s4 the cumulative pass should show 5 (since s1 passed in A, B, D and s4 passed in A, C,E)
Thanks.
Solved! Go to Solution.
Hi,
Thank you for your feedback.
Could you please check the below picture and the attached file?
I amended one of the measure like below.
Result of the course measure: =
SUMX (
VALUES ( Course[Course] ),
CALCULATE (
COUNTROWS (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Data[Status] ),
"@status", CALCULATE ( MAX ( Data[Status] ) )
),
[@status] = 1
)
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Total pass measure: =
SUM( Data[Status] )
Result of the course measure: =
COUNTROWS (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Data[Status] ),
"@status", CALCULATE ( MAX ( Data[Status] ) )
),
[@status] = 1
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks, @Jihwan_Kim for the reply, I am looking for the measured total (i,e Total pass measure). if all the students are selected the total should show 5 since there are 5 distinct courses and everyone passed at least once, and if we select s1 and s2 Total pass measure should show 4, since s1 passed in A, B, D & s2 in B, C
Hi,
Thank you for your feedback.
Could you please check the below picture and the attached file?
I amended one of the measure like below.
Result of the course measure: =
SUMX (
VALUES ( Course[Course] ),
CALCULATE (
COUNTROWS (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Data[Status] ),
"@status", CALCULATE ( MAX ( Data[Status] ) )
),
[@status] = 1
)
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim Quick follow-up question on the above, how can I have a view(table) which shows the courses not taken by the students
Thanks in advance.
Hi,
Please check the below picture and the attached pbix file.
Not taken courses measure: =
VAR _coursestaken =
VALUES ( Data[Course] )
VAR _condition =
IF ( VALUES ( Course[Course] ) IN _coursestaken, BLANK (), "Not Taken" )
RETURN
_condition
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |