problem with dax code in a measure

Question

Hello champs,&nbsp;I have following measure but it doesn't show me values(empty cells), it doesnt say error either. When I remove&nbsp;&nbsp;(SelectedYear - TopEventYear) &gt; 5 at the bottom , all works correctly , it shows the total, thats fine but I don't need total what I need to achieve extra condition "greater than 5 years from total" . Any idea how to achieve it? In Months would be great as well. which is 60. I tried many ways and now I'm lack of ideas.Measure:&nbsp;pokus6 = VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year]) &nbsp;RETURN &nbsp; &nbsp; &nbsp; COUNTROWS( &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseDetail,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR TopFromStateID =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE(CecCaseEvent[FromStateOfProceedingID]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TOPN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL(CecCaseEvent),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseEvent[CecCaseID] = CecCaseDetail[CecCaseId] &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseEvent[ToStateOfProceedingID] &lt;&gt; CecCaseEvent[FromStateOfProceedingID] &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR(CecCaseEvent[EventDate]) = SelectedYear &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseEvent[EventOrder],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DESC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR TopEventDate = &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX(CecCaseEvent[EventDate]), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseEvent[FromStateOfProceedingID] = 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR TopEventYear = YEAR(TopEventDate) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TopFromStateID IN {4} &amp;&amp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT(ISBLANK(TopEventYear)) &amp;&amp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (SelectedYear - TopEventYear) &gt; 5 &amp;&amp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNT(CecCaseDetail[CecCaseId]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseEvent[EventID] = 64,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR(CecCaseEvent[EventDate]) = SelectedYear,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseEvent[CecCaseId] = CecCaseEvent[CecCaseId]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) &amp;&amp; IF(ISBLANK(TopFromStateID), 0, TopFromStateID) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'CecLeadingCase',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'CecLeadingCase'[CecCaseId] = CecCaseDetail[CecCaseId]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )&nbsp;HERE is picture I tried to explain how it works , we need to achieve "the green arrow " greater more than 5 years from the red rectangle. Forgot to write in the picture the Event Date column is CecCaseEvent[EventDate].&nbsp;&nbsp;&nbsp;

rajendraongole1 · Answer

Hi&nbsp;Anonymous&nbsp; -&nbsp;Add a debug measure to check TopEventYear and SelectedYear separately to confirm they are correctly calculated.
&nbsp;
DebugMeasure = VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year]) VAR TopEventDate =CALCULATE(MAX(CecCaseEvent[EventDate]),CecCaseEvent[FromStateOfProceedingID] = 4)VAR TopEventYear = YEAR(TopEventDate)RETURN "Selected Year: " &amp; SELECTEDVALUE('Calendar'[Date].[Year]) &amp; " | Top Event Year: " &amp; IF(ISBLANK(TopEventYear), "Blank", FORMAT(TopEventYear, "0")) &amp;" | Difference: " &amp; IF(ISBLANK(TopEventYear), "N/A", FORMAT(SelectedYear - TopEventYear, "0"))
&nbsp;
Modify your measure to handle cases where TopEventYear is blank.
pokus6 =VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year]) VAR TopEventDate =CALCULATE(MAX(CecCaseEvent[EventDate]),CecCaseEvent[FromStateOfProceedingID] = 4)VAR TopEventYear = YEAR(TopEventDate)VAR YearDifference = IF(ISBLANK(TopEventYear), 0, SelectedYear - TopEventYear)RETURN COUNTROWS(FILTER(CecCaseDetail,VAR TopFromStateID =CALCULATE(SELECTEDVALUE(CecCaseEvent[FromStateOfProceedingID]),TOPN(1,FILTER(ALL(CecCaseEvent),CecCaseEvent[CecCaseID] = CecCaseDetail[CecCaseId] &amp;&amp;CecCaseEvent[ToStateOfProceedingID] &lt;&gt; CecCaseEvent[FromStateOfProceedingID] &amp;&amp;YEAR(CecCaseEvent[EventDate]) = SelectedYear),CecCaseEvent[EventOrder],DESC))RETURNTopFromStateID IN {4} &amp;&amp;NOT(ISBLANK(TopEventYear)) &amp;&amp;YearDifference &gt; 5 &amp;&amp;CALCULATE(COUNT(CecCaseDetail[CecCaseId]),CecCaseEvent[EventID] = 64,YEAR(CecCaseEvent[EventDate]) = SelectedYear,CecCaseEvent[CecCaseId] = CecCaseEvent[CecCaseId]) &amp;&amp; COUNTROWS(FILTER('CecLeadingCase','CecLeadingCase'[CecCaseId] = CecCaseDetail[CecCaseId]))))
&nbsp;
Instead of checking if the difference is greater than 5 years, check if it's greater than 60 months.
Replace (SelectedYear - TopEventYear) &gt; 5 with MonthDifference &gt; 60. check this.
&nbsp;

anonymous · Answer

sorry Iam lost now how to implement debug measure. Can you tell me what to do with the debug measure. I tried to fit in but it cause empty cell again

Forum Discussion

problem with dax code in a measure

2 Replies

Related content

Measure formula problem

Dax measure for below problem

Logic problem?

Measure problem (problem with ALL)

Slider problems