Measure to count based on date criteria from related table

[kamiluc]

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!

[Anonymous · Accepted solution]

Hi @kamiluc ,

Please try to create a measure with below dax formula:

Test =
VAR cur_date =
    SELECTEDVALUE ( 'Date'[Date] )
VAR tmp =
    FILTER ( ALL ( Control_Tests ), [TESTDATE] <= cur_date )
VAR tmp1 =
    SUMMARIZE ( tmp, [CONTROLID], "Max Date", MAXX ( Control_Tests, [TESTDATE] ) )
VAR tmp2 =
    SELECTCOLUMNS (
        ADDCOLUMNS ( tmp1, "Concate_Str", [CONTROLID] & [Max Date] ),
        "Concate_str", [Concate_Str]
    )
VAR tmp3 =
    FILTER ( tmp, [CONTROLID] & [TESTDATE] IN tmp2 )
VAR tmp4 =
    CALCULATETABLE (
        VALUES ( Control_Tests[CONTROLID] ),
        FILTER ( tmp3, [TESTRESULT] = "Fail" )
    )
VAR _a =
    COUNTROWS ( tmp4 )
RETURN
    IF ( ISBLANK ( _a ), 0, _a )

 Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kamiluc]

Thank you @Anonymous ! This worked perfectly!