Forum Discussion
Distinct Values where neither contains
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 |
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!
4 Replies
- amitchandak
Super User
Anonymous ,
refer if this can help
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
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?
v-zhenbw-msftCommunity Support
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.
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!
