I was having trouble putting my problem into words for a good title, but I hope I can detail here exactly what I'm looking to achieve.
Let's say I have tables: a student table, questions/answer table, and a student response table where a student id is mapped to a question/answer id. This report includes multiple semesters and each semester contains multiple units, all of which I want to be able to filter on for my visuals.
The student[cohort] can contain one of the following values: "Graduate", "Undergraduate","unassigned"
The questions/answer[cohort] column can contain: "Graduate", "Undergraduate", or "All". Only specific items are cohorted, so the vast majority are "All"
Though, for both of these, there could be potentially other cohorts added.
For the purpose of my question, I want to focus on the questions/answers table, but I wanted to detail the other tables to get an idea of how they are related and that I will likely use the student[cohort] to interact with the questions[cohort], for example finding average student course completeion, which would be innaccurate if I don't use count based on cohorts.
I would like to get a count of how many question/answers total based on the cohort associated with that question. My simple solution is something like this:
COUNTROWS(FILTER(questions/answers, [cohort] <> "All")) + COUNTROWS(FILTER(questions/answers, [cohort] == "All))
However filtering based on cohorts breaks down as "All" is considered a separate cohort, so if I wanted to do something like have a clustered column chart with the count as values, semester as the axis, and cohort as the legend, there would be 3 bars per cluster and All would outnumber everything. I considered making a calculated table that had a column that would sum based on cohort + "All", but this loses a lot of flexibility. If I had a slicer that allowed selection of the cohort, "All" would need to be always on and only one other cohort could be selected at a time.
The last thing I can think of would be on the data end, where I could parse a new row for both Undergrad and Grad instead of a single row that has a cohort of 'All'. This would eliminate the problem, but then I would be at best doubling nearly every row of my question/answer table with mostly redundant data. This would also break things for "unassigned" students which can interact with everything in the course I have labeled as 'All' and just can't see the cohorted questions.
Any advice is welcome, thank you!
