Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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!
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

2 REPLIES 2
Anonymous
Not applicable

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.

Frequent Visitor

Thank you @Anonymous ! This worked perfectly!

## Helpful resources

Announcements

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors