Distinct Values where neither contains

[Anonymous]

Hi All,

I'll need to create multiple reports where the structure of the data source has a hierarchical structure, and I need distinct counts of the higher levels of the hierarchy based on conditions in the lower parts of the hierarchy. The data source is SQL so I may have to write several queries to get separate tables if that's what's needed. I have a demo of what I'm looking at below:

This is 1 ticket, but there are 2 tasks and 3 subtasks. I need to report on "Ticket 1" and indicate that it was opened March 1st, and that the order hasn't been submitted yet. 

Ticket number	open date	close date	task number	task name	close date	subtask number	subtask name	close date
ticket1	mar 1		task1	Start new order	mar. 3	subtask1	call customer	mar. 2
ticket1	mar 1		task1	Start new order	mar. 3	subtask2	Confirm order details	mar. 2
ticket1	mar 1		task2	Submit Order		subtask1	Enter in system	mar.4

[v-zhenbw-msft]

Hi @Anonymous ,

We have a little confused about your issue.

What is your desire result? Do you want to create a matrix table to show the distinctcount like following?

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hello,

As I went back to the stakeholder asking for this information I better understand what the need is. I was originally miss-understanding the ask, however, I am still challenged with how to report a specific metric. 

What I am looking for at this point is how to count distinct ticket numbers between 2 different dates. I have seen numerous posts about this and will continue to find the information that I need on other posts.

Thank you for all of your support!

[amitchandak]

@Anonymous ,

refer if this can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Anonymous]

This is certainly helpful, however, my issue is that the lower levels of the hierarchy aren't dependent on the upper parts of the hierarchy. My situation is much-like a national car repair chain, whereby a repair can be quoted over the phone but then the customer still has to bring the car in to receive service. While the local team is waiting to bring in the car for service, the head office can start doing work such as ordering parts. This would mean that the parts order could complete before the car even arrives. At which point I would be looking to count how many cars have been repaired, are in the shop, are waiting to be picked up at any given moment a task/sub-task (related to parts, service, or other administrative actions) could be completed "out of order".

but the "car" is higher in the hierarchy than the part order, drop-off, or other tasks. In these instances I'm looking to count the unique ticket numbers where the "bring car into garage" task is not completed.

Would your solution apply to this and I'm just missing the connection?