Mismatch in status count

Question

Hi guys,&nbsp;I have the following situation:&nbsp;I am working with complaints, where we have a main complaint, and this is devided into multipe (or 1) 'subcomplaints'. Each complaint has its own status, e.g. completed, ongoing, delayed, etc.&nbsp;When the status of all subcomplaints is' completed', the main complaint also should have the status 'completed'. Now, i want to count the complaints where this isn't the case; so i want to count the cases where all subcomplaints have the status 'completed', but the main complaint still has another status.&nbsp;(The main- and subcomplaints are grouped by a unique id that they all share).&nbsp;&nbsp;

naveengandhi · Answer

Hello&nbsp;QwertyMartijn&nbsp;Try the below Dax.&nbsp;Main_Complaint = CALCULATE(&nbsp; &nbsp; COUNTROWS('complaint'),&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; 'complaint',&nbsp; &nbsp; &nbsp; &nbsp; 'complaint'[TypeKlacht] = "Main Complaint" &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; 'complaint'[Status] &lt;&gt; "Completed" &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS('complaint'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLEXCEPT('complaint', 'complaint'[ID]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'complaint'[TypeKlacht] = "Sub Complaint",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'complaint'[Status] &lt;&gt; "Completed"&nbsp; &nbsp; &nbsp; &nbsp; ) = 0&nbsp; &nbsp; ))&nbsp;Dataset i used and result.&nbsp;&nbsp;Let me know if this helps.&nbsp;If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the others find it more quickly.&nbsp;&nbsp;

qwertymartijn · Answer

Thanks for your quick reply. In the data you used, i dont think the correct count should be 1.

For both ID's 938831 and 867645 all subcomplaints are completed, but the status of the main complaint is still 'delegated. Therefore, i would like the code to return 2 counts.

naveengandhi · Answer

QwertyMartijn&nbsp;Please see for ID&nbsp;938831&nbsp;, One of the sub complaint is delegated, I changed it so to do a test case. With that sub complaint being changed to completed, The count is 2 as you expect.&nbsp;&nbsp;

qwertymartijn · Answer

You're right, i missed that. I'll check if you're code works for me right away!

qwertymartijn · Answer

I need one more adjustment in the code; the 'completed' status in the subcomplaint can be one of the three, depening on who handeled the complaint.&nbsp;I thought of something like this, but something goes wrong in the allexcept part.&nbsp;

Forum Discussion

Mismatch in status count

7 Replies

Related content

Type Mismatch

Detected Type Mismatches

Detected Type Mismatches

Counting nulls

Type mismatch