Help finding issue in measure

[kamiluc]

Hi,

I have a table (RISK_EVALUATION) that contains risk evaluations for RISKIDs (can be multiple) along with dates and outcomes. I then created a measure that I intent to include in a matrix to count how many risks have the latest evaluation as "Not Assessed".

The matrix will have the last day of the month from a calendar table as columns.  A RISKID should be counted on that month if the latest evaluation, even if done many months ago, had a value of "Not Assessed". It'd look like this:

	Sep-23	Oct-23	Nov-23	Dec-23
# Risks Not Assessed	5	5	4	4

My current measure:

# Risks Not Assessed =

var _selectdate = MAX(dCalendar[Date])

var tmp =  FILTER( ALL( RISK_EVALUATION), RISK_EVALUATION[RE_DATE] <= _selectdate) --create a temp table with evaluations only up to the date selected

var tmp1 = SUMMARIZE(tmp, [RISKID], "Max Date", MAXX( RISK_EVALUATION, RISK_EVALUATION[RE_DATE])) --create a temp table summarising risk by the latest evaluation date

var tmp2 = SELECTCOLUMNS( ADDCOLUMNS( tmp1, "Concate_Str", [RISKID] & [Max Date]), "Concate_Str", [Concate_Str]) --create a temp table with single column concatenating riskid and mre date

var tmp3 = FILTER( tmp, [RISKID] & [MRE_DATE] IN tmp2) --create temp table based on first tmp, filtering only those with the latest RE date for each risk

var tmp4 = CALCULATETABLE( VALUES( RISK_EVALUATION[RISKID]), FILTER( tmp3, [RE_RATING] = "Not Assessed" )) --create temp table for the risks where the latest RE is defined as not assessed. 

var _a = COUNTROWS(tmp4) + 0

var _risknotassessed = IF( ISBLANK(_a), 0, _a)

return

_risknotassessed

This however is not giving me the correct output. More specifically, when I apply some filters, the value of the measure is not impacted by the filters when it should.  I've looked at it enough and can't seem to find the issue so hoping the community could help me spot my mistake. Thanks!

[kamiluc · Accepted solution]

I have figure it out. My error was on the variable tmp1 which was producing a table with the latest date for all RISKIDs, rather than the latest for each RISKID.

I've replaced that variable by the following:

var tmp1 = 
SUMMARIZE(
FILTER(
FILTER(RISK_EVALUATION,RISK_EVALUATION[RE_DATE]<=_selectdate),
RISK_EVALUATION[RE_DATE]
= CALCULATE (
MAX ( RISK_EVALUATION[RE_DATE] ),
ALLEXCEPT ( RISK_EVALUATION, RISK_EVALUATION[RISKID] )
)
),[RISKID],[RE_DATE])

This actually eliminates the need for the variable tmp as well.