Hey all,
I'm having a tough time building out a certain visual and need some help. I'm trying to add a bar chart that displays the number of tests failed on the axis and a count of students as the value.
I have a fact table set up like so:
| StudentID | TestID | TestDate | Result |
| 1 | 1 | 1/1/2021 | Fail |
| 1 | 2 | 2/1/2021 | Fail |
| 2 | 3 | 1/2/2021 | Fail |
| 2 | 4 | 1/15/2021 | Fail |
My fact table is related to my Dim Calendar table in the data model.
I have been able to build the visual using the following measure and calculated table:
Times Failed = CALCULATE(COUNT('Fact Test Results'[TestID],'Fact Test Results'[Result]="Fail")
Repeat Fails = SUMMARIZE('Fact Test Results','Fact Test Results'[StudentID],"Fails",[Times Failed])
I then relate this calculated table to my fact table based on the StudentID.
This works to display my bar chart grouping the count of students by the number of times they failed a test.
BUT...
My problem is that I need this to be affected by my date slicer which is only related to the Fact table. When I set crossfiltering to both on the relationship between my calculated table and fact table, the date slicer seems to affect the calculated table...sorta.
The numbers will change, but they are not accurate. For example, if I select a specific day in my date slicer, in theory the axis of the bar chart should only contain a single group--the number of students that failed one time. But it doesn't, there are groups going all the way up to five failures which seem to indicate something is off.
Any ideas on how I can get this to work? Thanks!
