Measure to count based on date criteria from related table
Hi there, I've got 2 related tables where one is the "parent" for controls, and the other contains tests performed for the controls. It's a one-to-many relationship based on CONTROLID, as each control will have multiple tests performed. I'm trying to create a measure that will count how many controls have a failed status as the latest test in the period I'm analysing. The tables will be like this: Controls CONTROLID A B Control_Tests CONTROLID TESTDATE TESTRESULT A 1/Jan/23 Fail A 1/Jun/23 Pass B 1/Feb/23 Fail B 1/Jul/23 Pass B 1/Sep/23 Fail I have a calendar table which is not related to the above ones to use on a matrix, for example, so the date needs to come from that calendar table. So the outcome should be something like this: Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23 Jul-23 Aug-23 Sep-23 # Deficient Critical Controls 1 2 2 2 2 1 0 0 1 I think I have the logic halfway there, as I was able to create a measure that gives me the latest result per period for a given CONTROLID, and it looks like this: Latest Result = var _maxtestdate = MAX( dCalendar[last day of month]) var _currentdate = CALCULATE( MAX( Control_Tests[TESTDATE]), FILTER( Control_Tests, Control_Tests[TESTDATE] <= _maxtestdate)) return CALCULATE( MAX( Control_Tests[TESTRESULT] ), FILTER( Control_Tests, Control_Tests[TESTDATE] = _currentdate ) ) However, when I try to use the logic from that measure into another one for counting, I get zero quantity for all. This is the DAX currently not working: # Deficient Critical Controls = var _maxtestdate = MAX( dCalendar[last day of month]) var _currentdate = CALCULATE( MAX( Control_Tests[TESTDATE]), FILTER( Control_Tests, Control_Tests[TESTDATE] <= _maxtestdate)) var _latestperiodtest = CALCULATE( MAX( Control_Tests[TESTNRESULT] ), FILTER( Control_Tests, Control_Tests[TESTDATE] = _currentdate ) ) return CALCULATE( DISTINCTCOUNT(Controls[CONTROLID])+0, FILTER( Controls, _latestperiodtest = "Fail" ) ) I suspect I'm passing the _latestperiodtest incorrectly, but I don't understand exactly what and how to fix it. Any help would be greatly appreciated!
