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.
I have three tables, a student table, question/answer table, and student response table. The students are cohorted (eg Graduate, Undergraduate, Unassigned) and so are the questions, but including an All label that marks questions that are open to all cohorts. The course is self guided and optional, so ultimately I would like to be able to breakdown how students are interacting with this course. It is easy to count how many total questions a student responded to, but if I want to find a completion percentage (either average overal per semester or per student) I need to get the total count of questions and it needs to take into account the cohort and this is proving to be very difficult as any slicers would need to ignore filtering based on questions labeled "All".
The cohorts can be summarized into a table like this based on the student table:
And here is a simplified sample of how the question/answer table might look:
My goal is to be able to get counts by cohort and be able to use slicers based on semester, unit, cohort, etc and be able to make visuals, such as clustered bar graphs to show these counts with the cohort as the legend. Essentially I don't want my visuals to separate "All" as it's own category, but instead add the total count of "All" to each cohort total. Here are some examples based on the sample data I included above showing some ways I'd like to be able to get the counts:
In DAX, I was able to make this calculated table that can receate the first table, or second table if I include semester in the summarize with a script like this :
Cohort Table =
SUMMARIZE(
FILTER(
'student',
NOT(ISBLANK('student'[cohort]))
),
'student'[cohort],
"Count", COUNTROWS(FILTER('question answer','question answer'[cohort] = 'student'[cohort])) + COUNTROWS(FILTER('question answer', 'question answer'[cohort] = "All"))
)
however this is not dynamic so I wouldn't be able to apply slicers and breaking down by units because much more complicated. If I need to have it hardcoded, then having this count by semester and unit number would probably be usable for my purposes, but I worry that I may need to have more specific breakdowns based on other things (for example there are different types of question, so I may want to exclude those) and I would essentially need to redo the calculated table. I am ideally looking for a dynamic solution in a measure that returns a total and can work with slicers to get the breakdowns I am looking for. I have tried applying CALCULATE([Count of Cohort = all], ALL([cohort]) to some of my measure attempts but this doesn't seem to work and having pagewide slicers would need to somehow prevent the user from filtering out 'All' as cohorts otherwise. Any advice appreciated!
A simplistic approach would be to explode the "All" rows into the affected cohort rows. That would make your Power BI much easier, at the expense of slighly uglier (ie more verbose) source data.
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |